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