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.

> 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.

"Hugo Kornelis" wrote:

> 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