Re: Date format question

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:

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

.

Relevant Pages

  • Re: Using variables in Dynamic SQL
    ... >converting datetime from character string." ... the CONVERT function in this piece of the code to format the date back to ... If you print the resulting string in @sql, you'll see that you have some ... DECLARE @StartDate datetime ...
    (microsoft.public.sqlserver.programming)
  • RE: SqlCeEngine.CreateDataBase() - LCID - Datepart format problem
    ... Background info on why insert in your format doesn't work: ... datetime as string, SQL CE does an implicit convert from string to ... datetime formats with their style IDs are listed in CONVERT function in SQL ...
    (microsoft.public.sqlserver.ce)
  • Re: Inserting regional settings specific data into sql server
    ... All EU countries withouth the two English speaking use the format dd-MM-yy ... By using the parameters, while the program is in the right culture setting, ... than all those formats results using SQLparameters in the way the SQL server ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: VB & SAL Date/Time Problem?
    ... you don't need to be concerned with datetime ... SQL Server MVP ... > I've written an ASP app that requires users to login - normal stuff. ... > So I believe that what I need to do is to convert the date time format ...
    (microsoft.public.sqlserver.programming)
  • Re: Saving to a DateTime field in Sql Server 2000 using ADO.Net
    ... If you really must pass a DateTime as a string, ... of SQL server's culture netural formats. ... >I have a Datetime field in a Sql Server 2000 database, ... as the 'm/d/y' date format appears to be used when Sql ...
    (microsoft.public.dotnet.framework.adonet)