DateFormat

  • Comments posted to this topic are about the item DateFormat

  • Nice question, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

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

  • Nice question.

    Can to the conclusion it wouldn't with with offset, then it was a case of old datetime types vs new or all?

    I guessed wrong. But one to put in the meory banks for future use.

    Cheers,

    Rodders...

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • Well, I'm British and I don't know anybody who has ever used YDM format. We go for DMY or YMD.

    _____________________________________________________________________
    MCSA SQL Server 2012

  • Richard Warr (7/3/2012)


    Well, I'm British and I don't know anybody who has ever used YDM format. We go for DMY or YMD.

    I do, but it has been "by accident" in that if a server/database/connection/whatever is set up as british (as opposed to us-english) then using yyyy-mm-dd will have unexpected results since for datetimes and smalldatetimes it is interpreted as yyyy-dd-mm. It's why I always insist people use yyyymmdd format for dates.

  • Stewart "Arturius" Campbell (7/3/2012)


    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.

    Try sp_helplanguage @language = 'BRITISH' and check the dateformat column

    Thanks for the tip. I realised that the default format for BRITISH would be dmy after I posted, and running your code confirms that. However, when dealing with dates like '2008-04-05', BRITISH interprets it as 4th May 2008, not 5th April 2008 - i.e. as ydm rather than ymd. Again though, I can't remember where that behaviour is documented.

  • Thanks all for the feedback

  • Thanks for the question. I thought it was bad enough being in America where most everyone uses mmddyyyy. But, yyyyddmm makes even less sense to me. Anyone know why thats used?

    Also, how about another new function:

    sp_helpdriver @driver = N'British'

    Result = 'Left side of road'

    :hehe:

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • Thomas Abraham (7/3/2012)


    Thanks for the question. I thought it was bad enough being in America where most everyone uses mmddyyyy. But, yyyyddmm makes even less sense to me. Anyone know why thats used?

    There might be someone who does, but this thread from someone trying to find out makes entertaining reading (he never got his answer): http://social.msdn.microsoft.com/Forums/en-AU/sqldatabaseengine/thread/04d9917d-fc05-4e3e-ba70-0e8a3eac3fe8

    Thomas Abraham (7/3/2012)


    Also, how about another new function:

    sp_helpdriver @driver = N'British'

    Result = 'Left side of road'

    :hehe:

    It could have other parameters

    sp_helpdriver @driver = N'British', @type=N'Taxi'

    Result = 'Pretty much anywhere he pleases'

  • Excellent question. Thank you for that and Duncan thank you for that entertaining read. Poor guy, I kinda felt sorry for him at the end. I guess this is just gonna get put into that category of "The answer is 42". :hehe:



    Everything is awesome!

  • Duncan Pryde (7/3/2012)


    There might be someone who does, but this thread from someone trying to find out makes entertaining reading (he never got his answer): http://social.msdn.microsoft.com/Forums/en-AU/sqldatabaseengine/thread/04d9917d-fc05-4e3e-ba70-0e8a3eac3fe8

    Thanks for the link. I too felt sorry for the guy. Caused me to go on a hunt for a better answer. Found a few similar posts, but no one seems to have an answer. It does appear to be an artifact of an attempt at backwards compatibility with perhaps undesirable consequences. I came across this, which almost sheds light on the subject: http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=290971

    Shows that in 2007 someone at Microsoft thought that:

    1. YYYY-DD-MM should be deprecated.

    2. THey knew that no culture actually used that format.

    And now, back to work that pays, little as that may be.

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • Thank you for the question.

    I also agree that YYYYDDMM is rarely used anywhere, and I use YYYYMMDD as standard, as it is easier to interpret in any situation.

    "El" Jerry.

    "El" Jerry.

    "A watt of Ottawa" - Gerardo Galvan

    To better understand your help request, please follow these best practices.[/url]

Viewing 15 posts - 1 through 15 (of 23 total)

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