Post reply

DateFormat

  • July 3, 2012 at 1:17 pm

    #1508450

    d'oh missed it because I inverted my answer.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • July 3, 2012 at 1:32 pm

    #1508457

    This is actually related to the 2012-06-06 QotD. If you look at Books Online for the DATE, DATETIME2, and DATETIMEOFFSET data types, you will see that they have a default string literal that is used for implicit conversions.

    If you run the following code, the first will fail and the second two will work.

    SET DATEFORMAT YDM;

    DECLARE @mydate DATE;

    SELECT @mydate = '2012-21-01';

    SELECT @mydate;

    go

    DECLARE @mydate DATE;

    SELECT @mydate = '2012-01-21';

    SELECT @mydate;

    GO

    DECLARE @mydate DATE;

    SELECT @mydate = CAST('2012-21-01' AS DATETIME);

    SELECT @mydate;

    GO

  • July 3, 2012 at 9:46 pm

    #1508562

    Thanks for sharing. 🙂

  • July 3, 2012 at 10:09 pm

    #1508573

    Sigh. Ticked all the 'new' data types except 'date' for some reason. Clearly having an off day.

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • July 3, 2012 at 11:35 pm

    #1508584

    There is no error for datetime2. I think answer should be date.

  • July 4, 2012 at 1:47 am

    #1508625

    anil_24apr (7/3/2012)

    There is no error for datetime2. I think answer should be date.

    Not sure what you are talking about. I took my code from above and changed DATE to DATETIME2 and it errors just the same way as DATE.

  • July 20, 2012 at 12:02 pm

    #1515687

    Duncan Pryde (7/3/2012)

    Good question. Is this related to the behaviour of datetimes versus datetime2s with the british language setting?

    SET LANGUAGE BRITISH

    GO

    DECLARE @datevar datetime2 = '2008-12-13'; -- no error, ymd format

    SELECT @datevar;

    GO

    DECLARE @datevar datetime = '2008-12-13'; -- error, ydm format

    SELECT @datevar;

    GO

    I can't find a specific reference, but it looks as though SET LANGUAGE BRITISH implicitly sets the dateformat to ydm for datetimes and smalldatetimes and to ymd for dates and datetime2s.

    I guess it must be. And British people (like me) have great difficulty understanding why on earth this should be - we always use ymd or dmy, unlike Americans who tend to use myd. When month names (as opposed to numbers) are used we use d mon y or mon d y about equally, but dateformat is about the numeric formats.

    edit: And please, everyone, vote for the connect item that Thomas dug up.

    Tom

  • August 8, 2012 at 12:07 pm

    #1523318

    Nice question.

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply