Hello All
I am stuck in silly point Please help me and excuse me for a bad
Knowledege
of SQL store Procedurte Programming.
My date are store in DB as 10/17/2005 5:37:07 PM
How Do I fetch the Name of month from this.
Thanking You
With regards
Tarun Sinhaselect datename(month,getdate())
HTH. Ryan
<tarun.sinha@.gmail.com> wrote in message
news:1143020683.081697.80810@.i39g2000cwa.googlegroups.com...
> Hello All
> I am stuck in silly point Please help me and excuse me for a bad
> Knowledege
> of SQL store Procedurte Programming.
> My date are store in DB as 10/17/2005 5:37:07 PM
> How Do I fetch the Name of month from this.
>
> Thanking You
> With regards
> Tarun Sinha
>|||Hello Dear
Thanks for replying me so soon
I have a number of Date in DB let say,
Code Userid AssigningDate
fjjfjsddj 14 10/17/2005 5:37:07 PM
fjjfjsddj 14 10/17/2005 5:37:07 PM
fjjfjsddj 14 11/17/2005 5:37:07 PM
fjjfjsddj 14 11/17/2005 5:37:07 PM
fjjfjsddj 14 11/17/2005 5:37:07 PM
fjjfjsddj 12 9/17/2005 5:37:07 PM
fjjfjsddj 11 9/17/2005 5:37:07 PM
Now I want to Fetch all Those User Whose falls on same Month.
and also want to Know the Name of Month.
very Obiligied for You
With regards
Tarun Sinha|||select DATENAME(month,AssigningDate), * from tablename
WHERE DATENAME(month,AssigningDate) = 'March'
HTH. Ryan
<tarun.sinha@.gmail.com> wrote in message
news:1143021721.983590.131880@.t31g2000cwb.googlegroups.com...
> Hello Dear
>
> Thanks for replying me so soon
> I have a number of Date in DB let say,
> Code Userid AssigningDate
> fjjfjsddj 14 10/17/2005 5:37:07 PM
> fjjfjsddj 14 10/17/2005 5:37:07 PM
> fjjfjsddj 14 11/17/2005 5:37:07 PM
> fjjfjsddj 14 11/17/2005 5:37:07 PM
> fjjfjsddj 14 11/17/2005 5:37:07 PM
> fjjfjsddj 12 9/17/2005 5:37:07 PM
> fjjfjsddj 11 9/17/2005 5:37:07 PM
>
> Now I want to Fetch all Those User Whose falls on same Month.
> and also want to Know the Name of Month.
>
> very Obiligied for You
> With regards
> Tarun Sinha
>|||Thank You dear
You Help me a lot ..
With regards
Tarun Sinha
Showing posts with label stuck. Show all posts
Showing posts with label stuck. Show all posts
Tuesday, March 27, 2012
Thursday, March 8, 2012
Date format question
I think this should be easy, but I'm stuck. I have a datetime field with this
format: mm/dd/yyyy. It looks exactly as that format when I opened the table
in EM. How come when I used Query Analyzer, the format became yyyy-mm-dd
hh:mm:ss?
I need to create a crosstab table from this table using the sp here:
http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables with the date
field as the column heading. When I ran it, the column heading became Jan 1
2006 12:00AM, Feb 1 2006 12:00AM, etc. I want to see 01/01/2006, 02/01/2006,
etc instead. But I don't want to use convert because the date will be
converted to string and the columns won't be sorted by date anymore instead
it will be sorted like this: 01/01/2006, 01/01/2007, 02/01/2006, etc.
I hope this makes sense. Thanks in advance.
On Wed, 1 Aug 2007 13:46:03 -0700, lwidjaya wrote:
>I think this should be easy, but I'm stuck. I have a datetime field with this
>format: mm/dd/yyyy.
Hi lwidjaya,
Actually, you don't. You have a datetime column, period. Datetime values
are stored in an internal format that is not even comprehensible to
humans. The format you see depends on whatever formatting is applied by
the client.
> It looks exactly as that format when I opened the table
>in EM. How come when I used Query Analyzer, the format became yyyy-mm-dd
>hh:mm:ss?
Because QA uses other formatting than EM.
>I need to create a crosstab table from this table using the sp here:
>http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables with the date
>field as the column heading. When I ran it, the column heading became Jan 1
>2006 12:00AM, Feb 1 2006 12:00AM, etc. I want to see 01/01/2006, 02/01/2006,
>etc instead. But I don't want to use convert because the date will be
>converted to string and the columns won't be sorted by date anymore instead
>it will be sorted like this: 01/01/2006, 01/01/2007, 02/01/2006, etc.
>I hope this makes sense. Thanks in advance.
You could of course use CONVERT with a format that won't mess up your
sorting (yyyy-mm-dd, for instance), but I think you'd better use a
completely different technique for the dynamic crosstab. The sqlteam
article uses at least one technique that is undocumented, unsupported,
and known to be unreliable in some cases, AND it uses a global temporary
table which means you'll get very interesting side effects if two users
execute it at the same time. There might be more issues, I couldn't
figure out what the hell this thing was doing after five minutes of
studying and I doubt you do understand everything - but the key to at
least somewhat reducing SQL injection risk when constructing dynamic SQL
is understanding every bit of the code.
Why not do something like this instead - still not completely safe from
all forms of SQL injection, but probably a lot better than what you're
using now.
USE pubs
go
DECLARE @.ord_date datetime,
@.sql nvarchar(4000);
SET @.sql = 'SELECT stor_id,'
DECLARE AllDates CURSOR FAST_FORWARD
FOR
SELECT DISTINCT ord_date
FROM dbo.sales
ORDER BY ord_date;
OPEN AllDates;
FETCH NEXT FROM AllDates INTO @.ord_date;
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.sql = @.sql + 'SUM(CASE WHEN ord_date = '''
+ CONVERT(char(8), @.ord_date, 112)
+ ''' THEN qty ELSE 0 END) AS "'
+ CONVERT(char(10), @.ord_date, 101) + '",'
FETCH NEXT FROM AllDates INTO @.ord_date;
END;
CLOSE AllDates;
DEALLOCATE AllDates;
SET @.sql = STUFF(@.sql, LEN(@.sql), 1, ' FROM dbo.sales GROUP BY stor_id,
ord_date;');
PRINT (@.sql);
--EXEC (@.sql);
go
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|||Hi Hugo,
Thanks a lot! That's what I need. It even replaces all 'Null' with zero.
Thanks again!
"Hugo Kornelis" wrote:
> On Wed, 1 Aug 2007 13:46:03 -0700, lwidjaya wrote:
>
> Hi lwidjaya,
> Actually, you don't. You have a datetime column, period. Datetime values
> are stored in an internal format that is not even comprehensible to
> humans. The format you see depends on whatever formatting is applied by
> the client.
>
> Because QA uses other formatting than EM.
>
> You could of course use CONVERT with a format that won't mess up your
> sorting (yyyy-mm-dd, for instance), but I think you'd better use a
> completely different technique for the dynamic crosstab. The sqlteam
> article uses at least one technique that is undocumented, unsupported,
> and known to be unreliable in some cases, AND it uses a global temporary
> table which means you'll get very interesting side effects if two users
> execute it at the same time. There might be more issues, I couldn't
> figure out what the hell this thing was doing after five minutes of
> studying and I doubt you do understand everything - but the key to at
> least somewhat reducing SQL injection risk when constructing dynamic SQL
> is understanding every bit of the code.
> Why not do something like this instead - still not completely safe from
> all forms of SQL injection, but probably a lot better than what you're
> using now.
> USE pubs
> go
> DECLARE @.ord_date datetime,
> @.sql nvarchar(4000);
> SET @.sql = 'SELECT stor_id,'
> DECLARE AllDates CURSOR FAST_FORWARD
> FOR
> SELECT DISTINCT ord_date
> FROM dbo.sales
> ORDER BY ord_date;
> OPEN AllDates;
> FETCH NEXT FROM AllDates INTO @.ord_date;
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.sql = @.sql + 'SUM(CASE WHEN ord_date = '''
> + CONVERT(char(8), @.ord_date, 112)
> + ''' THEN qty ELSE 0 END) AS "'
> + CONVERT(char(10), @.ord_date, 101) + '",'
> FETCH NEXT FROM AllDates INTO @.ord_date;
> END;
> CLOSE AllDates;
> DEALLOCATE AllDates;
> SET @.sql = STUFF(@.sql, LEN(@.sql), 1, ' FROM dbo.sales GROUP BY stor_id,
> ord_date;');
> PRINT (@.sql);
> --EXEC (@.sql);
> go
>
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
>
|||On Fri, 3 Aug 2007 11:32:01 -0700, lwidjaya wrote:
>Hi Hugo,
>Thanks a lot! That's what I need. It even replaces all 'Null' with zero.
Hi lwidjaya,
Just in case you'd rather have the NULL, you only have to leave out the
"ELSE NULL" part of the dynamically built query.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
format: mm/dd/yyyy. It looks exactly as that format when I opened the table
in EM. How come when I used Query Analyzer, the format became yyyy-mm-dd
hh:mm:ss?
I need to create a crosstab table from this table using the sp here:
http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables with the date
field as the column heading. When I ran it, the column heading became Jan 1
2006 12:00AM, Feb 1 2006 12:00AM, etc. I want to see 01/01/2006, 02/01/2006,
etc instead. But I don't want to use convert because the date will be
converted to string and the columns won't be sorted by date anymore instead
it will be sorted like this: 01/01/2006, 01/01/2007, 02/01/2006, etc.
I hope this makes sense. Thanks in advance.
On Wed, 1 Aug 2007 13:46:03 -0700, lwidjaya wrote:
>I think this should be easy, but I'm stuck. I have a datetime field with this
>format: mm/dd/yyyy.
Hi lwidjaya,
Actually, you don't. You have a datetime column, period. Datetime values
are stored in an internal format that is not even comprehensible to
humans. The format you see depends on whatever formatting is applied by
the client.
> It looks exactly as that format when I opened the table
>in EM. How come when I used Query Analyzer, the format became yyyy-mm-dd
>hh:mm:ss?
Because QA uses other formatting than EM.
>I need to create a crosstab table from this table using the sp here:
>http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables with the date
>field as the column heading. When I ran it, the column heading became Jan 1
>2006 12:00AM, Feb 1 2006 12:00AM, etc. I want to see 01/01/2006, 02/01/2006,
>etc instead. But I don't want to use convert because the date will be
>converted to string and the columns won't be sorted by date anymore instead
>it will be sorted like this: 01/01/2006, 01/01/2007, 02/01/2006, etc.
>I hope this makes sense. Thanks in advance.
You could of course use CONVERT with a format that won't mess up your
sorting (yyyy-mm-dd, for instance), but I think you'd better use a
completely different technique for the dynamic crosstab. The sqlteam
article uses at least one technique that is undocumented, unsupported,
and known to be unreliable in some cases, AND it uses a global temporary
table which means you'll get very interesting side effects if two users
execute it at the same time. There might be more issues, I couldn't
figure out what the hell this thing was doing after five minutes of
studying and I doubt you do understand everything - but the key to at
least somewhat reducing SQL injection risk when constructing dynamic SQL
is understanding every bit of the code.
Why not do something like this instead - still not completely safe from
all forms of SQL injection, but probably a lot better than what you're
using now.
USE pubs
go
DECLARE @.ord_date datetime,
@.sql nvarchar(4000);
SET @.sql = 'SELECT stor_id,'
DECLARE AllDates CURSOR FAST_FORWARD
FOR
SELECT DISTINCT ord_date
FROM dbo.sales
ORDER BY ord_date;
OPEN AllDates;
FETCH NEXT FROM AllDates INTO @.ord_date;
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.sql = @.sql + 'SUM(CASE WHEN ord_date = '''
+ CONVERT(char(8), @.ord_date, 112)
+ ''' THEN qty ELSE 0 END) AS "'
+ CONVERT(char(10), @.ord_date, 101) + '",'
FETCH NEXT FROM AllDates INTO @.ord_date;
END;
CLOSE AllDates;
DEALLOCATE AllDates;
SET @.sql = STUFF(@.sql, LEN(@.sql), 1, ' FROM dbo.sales GROUP BY stor_id,
ord_date;');
PRINT (@.sql);
--EXEC (@.sql);
go
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|||Hi Hugo,
Thanks a lot! That's what I need. It even replaces all 'Null' with zero.
Thanks again!
"Hugo Kornelis" wrote:
> On Wed, 1 Aug 2007 13:46:03 -0700, lwidjaya wrote:
>
> Hi lwidjaya,
> Actually, you don't. You have a datetime column, period. Datetime values
> are stored in an internal format that is not even comprehensible to
> humans. The format you see depends on whatever formatting is applied by
> the client.
>
> Because QA uses other formatting than EM.
>
> You could of course use CONVERT with a format that won't mess up your
> sorting (yyyy-mm-dd, for instance), but I think you'd better use a
> completely different technique for the dynamic crosstab. The sqlteam
> article uses at least one technique that is undocumented, unsupported,
> and known to be unreliable in some cases, AND it uses a global temporary
> table which means you'll get very interesting side effects if two users
> execute it at the same time. There might be more issues, I couldn't
> figure out what the hell this thing was doing after five minutes of
> studying and I doubt you do understand everything - but the key to at
> least somewhat reducing SQL injection risk when constructing dynamic SQL
> is understanding every bit of the code.
> Why not do something like this instead - still not completely safe from
> all forms of SQL injection, but probably a lot better than what you're
> using now.
> USE pubs
> go
> DECLARE @.ord_date datetime,
> @.sql nvarchar(4000);
> SET @.sql = 'SELECT stor_id,'
> DECLARE AllDates CURSOR FAST_FORWARD
> FOR
> SELECT DISTINCT ord_date
> FROM dbo.sales
> ORDER BY ord_date;
> OPEN AllDates;
> FETCH NEXT FROM AllDates INTO @.ord_date;
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.sql = @.sql + 'SUM(CASE WHEN ord_date = '''
> + CONVERT(char(8), @.ord_date, 112)
> + ''' THEN qty ELSE 0 END) AS "'
> + CONVERT(char(10), @.ord_date, 101) + '",'
> FETCH NEXT FROM AllDates INTO @.ord_date;
> END;
> CLOSE AllDates;
> DEALLOCATE AllDates;
> SET @.sql = STUFF(@.sql, LEN(@.sql), 1, ' FROM dbo.sales GROUP BY stor_id,
> ord_date;');
> PRINT (@.sql);
> --EXEC (@.sql);
> go
>
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
>
|||On Fri, 3 Aug 2007 11:32:01 -0700, lwidjaya wrote:
>Hi Hugo,
>Thanks a lot! That's what I need. It even replaces all 'Null' with zero.
Hi lwidjaya,
Just in case you'd rather have the NULL, you only have to leave out the
"ELSE NULL" part of the dynamically built query.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Friday, February 17, 2012
Date Conversion
I'm hoping someone here can help me out because I am incredibly stuck on an issue in Crystal. I'm currently trying to take a string similar to the following "15:39:40" and change it into a time or numeric format so I can do some calculations on it. Unfortunatly every attempted to convert this using functions like Mid to seperate only the numbers then ToNumber to do the numeric conversion has met with total failure. I'm not new to coding but I am at this point very very lost as to how crystal wants to allow you to convert this data can anyone explain to me how in the heck to do this?Why not fetch the data as a string from you database using a to_char function and then use mid etc. for editing. If you use CR 10 or newer you can create SQL functions from Crystal Reports
- Jukka|||Hi,
I'm new to Crystal Reports 10, but not to SQL and I'm having an issue. I can't use SQL to create a stored procedure to be run by Crystal, so I'm having to go to the database directly from Crystal.
I have a date field that is really an integer: 20070612. I need to convert this field to be 06-12-2007 in Crystal. I have no way of doing so and do not even know where to begin.
The date will then be used to compare to today's date to determine if the record is returned or not.
Anyone have any thoughts?
Thanks!|||try out this
local Stringvar x:=cstr({tabla.dato},"###");
mid(x,5,2) + "-" + mid(x,7,2) + "-" + mid(x,1,4)|||What's wrong with the simple, obvious way? e.g.
stringvar x := "15:39:40";
ctime(x)
or what are you not telling us when you wrote 'take a string SIMILAR to'?|||What's wrong with the simple, obvious way? e.g.
stringvar x := "15:39:40";
ctime(x)
or what are you not telling us when you wrote 'take a string SIMILAR to'?
sorry :)
I have a date field that is really an integer: 20070612. I need to convert this field to be 06-12-2007 in Crystal. I have no way of doing so and do not even know where to begin.
local Stringvar x:=cstr({tabla.dato},"###");
mid(x,5,2) + "-" + mid(x,7,2) + "-" + mid(x,1,4)|||If its a string while fetching it from database you can convert it to datetime.|||Hensa, I was answering the thread's original question, not Bentley's one.
- Jukka|||Hi,
I'm new to Crystal Reports 10, but not to SQL and I'm having an issue. I can't use SQL to create a stored procedure to be run by Crystal, so I'm having to go to the database directly from Crystal.
I have a date field that is really an integer: 20070612. I need to convert this field to be 06-12-2007 in Crystal. I have no way of doing so and do not even know where to begin.
The date will then be used to compare to today's date to determine if the record is returned or not.
Anyone have any thoughts?
Thanks!|||try out this
local Stringvar x:=cstr({tabla.dato},"###");
mid(x,5,2) + "-" + mid(x,7,2) + "-" + mid(x,1,4)|||What's wrong with the simple, obvious way? e.g.
stringvar x := "15:39:40";
ctime(x)
or what are you not telling us when you wrote 'take a string SIMILAR to'?|||What's wrong with the simple, obvious way? e.g.
stringvar x := "15:39:40";
ctime(x)
or what are you not telling us when you wrote 'take a string SIMILAR to'?
sorry :)
I have a date field that is really an integer: 20070612. I need to convert this field to be 06-12-2007 in Crystal. I have no way of doing so and do not even know where to begin.
local Stringvar x:=cstr({tabla.dato},"###");
mid(x,5,2) + "-" + mid(x,7,2) + "-" + mid(x,1,4)|||If its a string while fetching it from database you can convert it to datetime.|||Hensa, I was answering the thread's original question, not Bentley's one.
Subscribe to:
Posts (Atom)