none
SET LANGUAGE british causes date format to change to YYYY-DD-MM RRS feed

  • Question

  • Hi,

    In the uk we write dates as DD/MM/YYYY to avoid confusion between uk, us and other countires dates when programming we tend to use the ANSI standard format YYYY-MM-DD

    We are seeing some really strange behaviour in SQL server 2008 when a user has their language set to british. The server start interpreting dates in a query as YYYY-DD-MM!

    Can anyone shed any light on this and test to see if it is just our server?!?

    To test this find a table with a datetime column and run the following

    SET LANGUAGE british

    SELECT * FROM yourtable WHERE yourdatetimecolumn > '2010-10-25'

    SET LANGUAGE us_english

    SELECT * FROM yourtable WHERE yourdatetimecolumn > '2010-10-25'

    Wednesday, December 8, 2010 2:05 PM

Answers

  • Execute below:

    EXEC sp_helplanguage 'british'

    Note that this returns dateformat dmy. Your ask why below query work:

    SET LANGUAGE british
    SELECT CAST('2003-28-02' AS datetime)

    Even though above query doesn't match dmy (the year is incorrect), SQL Server will figure out the year part, that it is in the wrong place and sort that part out. So, if we disregard the year part, you have dm, which corresponds to the format you specify in the aove SELECT (the day part comes before the month part).

    So, it isn't that yyyy-dd-mm is associated with British. What is associated with british is dmy (as in 2.23.2994, 2/23/2004, 2-23-2002 etc), and SQL Server is"smart" to figure out the year part.


    Tibor Karaszi, SQL Server MVP | web | blog
    • Marked as answer by David Cornall Wednesday, December 8, 2010 5:13 PM
    Wednesday, December 8, 2010 5:00 PM
  • One small edit to Tibors excellent answer

    as in 2.23.2994, 2/23/2004, 2-23-2002 etc

    should read

    as in 23.2.2994, 23/2/2004, 23-2-2002 etc

    • Marked as answer by David Cornall Wednesday, December 8, 2010 5:27 PM
    Wednesday, December 8, 2010 5:27 PM

All replies

  • Use YYYYMMDD format.

    http://www.karaszi.com/SQLServer/info_datetime.asp


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, December 8, 2010 2:09 PM
  • Hi Uri,

    I understand that YYYYMMDD works but would like an explanation as to why when language is set to british SQL server interperates a date written 2010-10-25 as YYYY-DD-MM and throws and error.

    YYYY-DD-MM is not a date format that would ever be used in the UK?!?

    Thanks Dave

    Wednesday, December 8, 2010 2:20 PM
  • Hello,

    Tibor explains that on the following article:

    http://www.karaszi.com/SQLServer/info_datetime.asp (Warnings and common misconceptions - SET DATEFORMAT)

    Hope this helps.   
     
    Regards,

    Alberto Morillo
    SQLCoffee.com

    Wednesday, December 8, 2010 2:45 PM
    Moderator
  • Thanks,

    From the article :-

    “SET LANGUAGE us_english
    SELECT CAST('2003-02-28' AS datetime)
    -----------------------
    2003-02-28 00:00:00.000

    SET LANGUAGE british
    SELECT CAST('2003-02-28' AS datetime)
    Server: Msg 242, Level 16, State 3, Line 4
    The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

    Please read the error message closely. It says exactly what the problem is. You specify the datetime value as a string, and because the string isn't formed according to the format you are using and the DATEFORMAT setting, SQL Server cannot convert the string to a datetime value.”

    I understand that datetime is not language neutral the part I don't understand is the following line "You specify the datetime value as a string, and because the string isn't formed according to the format you are using and the DATEFORMAT setting"

    Why does the following work?

    SET LANGUAGE british
    SELECT CAST('2003-28-02' AS datetime)

    I want to understand how and why the date format yyyy-dd-mm is associated with the language British?

     

    Wednesday, December 8, 2010 3:22 PM
  • Execute below:

    EXEC sp_helplanguage 'british'

    Note that this returns dateformat dmy. Your ask why below query work:

    SET LANGUAGE british
    SELECT CAST('2003-28-02' AS datetime)

    Even though above query doesn't match dmy (the year is incorrect), SQL Server will figure out the year part, that it is in the wrong place and sort that part out. So, if we disregard the year part, you have dm, which corresponds to the format you specify in the aove SELECT (the day part comes before the month part).

    So, it isn't that yyyy-dd-mm is associated with British. What is associated with british is dmy (as in 2.23.2994, 2/23/2004, 2-23-2002 etc), and SQL Server is"smart" to figure out the year part.


    Tibor Karaszi, SQL Server MVP | web | blog
    • Marked as answer by David Cornall Wednesday, December 8, 2010 5:13 PM
    Wednesday, December 8, 2010 5:00 PM
  • Thanks that explains it. Although I question the "smartness" of working out the year part is in the wrong place!! It dosen't seem very smart to me at all! I would love to hear from the designer who gave SQL server this ability to be "smart"!!!

    Anyway I have seen many an argument against using yyyy-mm-dd anyway so I shall stick to yyyymmdd unfortunately I deal with many legacy applications where yyyy-mm-dd has been used and if the language for a user is set to British as above this is then interprated as yyyy-dd-mm! or as you suggest dd-mm-yyyy once SQL server has very cleverly moved the year to it's correct position!!!

    Thanks Dave

    Wednesday, December 8, 2010 5:17 PM
  • One small edit to Tibors excellent answer

    as in 2.23.2994, 2/23/2004, 2-23-2002 etc

    should read

    as in 23.2.2994, 23/2/2004, 23-2-2002 etc

    • Marked as answer by David Cornall Wednesday, December 8, 2010 5:27 PM
    Wednesday, December 8, 2010 5:27 PM
  • << Although I question the "smartness" of working out the year part is in the wrong place!!>>

    Hehe, I never said I agree with the design. This is heritage since mid-eighties and it just can't be changed because of backward compatibility issue (breaking apps is bad, even if they rely on stupid design). If you instead were using the new date types, then all "separated/numeric" formats will be interpreted language neutral (yyyy-mm-dd).

    Best design would have been if SQL Server only accepted yyyy-mm-dd, but as soon as version 1.0 were out the door, we are stuck with what we had. The earliest point MS had a chance to do anything about this was in 2008, with the new date-related types.


    Tibor Karaszi, SQL Server MVP | web | blog
    Wednesday, December 8, 2010 5:31 PM
  • One small edit to Tibors excelent answer

    as in 2.23.2994, 2/23/2004, 2-23-2002 etc

    should read as

    in 23.2.2994, 23/2/2004, 23-2-2002 etc


    Ah, yes. Thanks for catching that, David.
    Tibor Karaszi, SQL Server MVP | web | blog
    Wednesday, December 8, 2010 5:35 PM
  • << Although I question the "smartness" of working out the year part is in the wrong place!!>>

    Hehe, I never said I agree with the design. This is heritage since mid-eighties and it just can't be changed because of backward compatibility issue (breaking apps is bad, even if they rely on stupid design). If you instead were using the new date types, then all "separated/numeric" formats will be interpreted language neutral (yyyy-mm-dd).

    Best design would have been if SQL Server only accepted yyyy-mm-dd, but as soon as version 1.0 were out the door, we are stuck with what we had. The earliest point MS had a chance to do anything about this was in 2008, with the new date-related types.


    Tibor Karaszi, SQL Server MVP | web | blog

    :-) Sorry didn't mean to imply that you agreed I was just having a rant :-) Thanks for your help. I really like to understand why something is the way it is rather than just accepting that I should use yyyymmdd when using datetime.
    Wednesday, December 8, 2010 5:53 PM