none
SQL Server incorrectly parsing dates as YYYY-DD-MM

    Soru

  • My SQL Server is incorrectly parsing dates as YYYY-DD-MM:

    DECLARE @x datetime SET @x = '2012-02-01' -- Should be February 1st SELECT @x

    (No column name)
    2012-01-02 00:00:00.000

    (Is parsed as January 2nd)

    The Default Language for the Server and User appears to be British English. Surely British English isn't YYYY-DD-MM? I tried changing the users' Language to just English without success.

    How do I fix this?

    08 Mart 2012 Perşembe 01:03

Yanıtlar

  • You should never depend on an implicit conversion of a date string to a datetime type.  There are far to many variables and the implicit conversion makes it best guess.  You should always use the CONVERT function to convert string.

    SET @x = CONVERT(datetime,'2012-02-01',120)

    • Yanıt Olarak Öneren Peja Tao 09 Mart 2012 Cuma 03:27
    • Yanıt Olarak İşaretleyen Peja Tao 15 Mart 2012 Perşembe 07:08
    08 Mart 2012 Perşembe 13:32

Tüm Yanıtlar

  • Further to this, if I disconnect and reconnect the user (with the English setting instead of British English), it works.

    So the question now is: Why is British English in YYYYDDMM format?

    08 Mart 2012 Perşembe 01:17
  • YYYY-MM-DD is language dependent way of passing date(s)

    SET LANGUAGE us_english 
    SELECT CAST('2003-02-28' AS datetime) 
    --output 2003-02-28 00:00:00.000
    SET LANGUAGE british 
    SELECT CAST('2003-02-28' AS datetime) 
    --outputs  an error 

    what is the output of

    DECLARE @x datetime
    SET @x = '20120201' -- Should be February 1st
    SELECT @x

    It should correctly output the date... regardless of the language setting.

    Please take some time and read Tibor Karaszi's article on SQL server date time datatype. 

    • Düzenleyen Chirag Shah 08 Mart 2012 Perşembe 01:48
    • Yanıt Olarak Öneren Peja Tao 09 Mart 2012 Cuma 07:50
    08 Mart 2012 Perşembe 01:42
  • Thanks Chriag - but it doesn't really answer my question:

    Why is British English in YYYYDDMM format? This format doesn't make sense.

    08 Mart 2012 Perşembe 02:04
  • Why is British English in YYYYDDMM format? This format doesn't make sense.

    As I said earlier, script you posted passes date in ANSI SQL format YYYY-MM-DD  which is language dependent (us english or british etc.) ...and SQL server will interpret the date based on the current langaguage setting (you originally had british english) that is why output displayed is showing you date in YYYYDDMM format.

    08 Mart 2012 Perşembe 02:19
  • This still doesn't answer the question.

    My boss is English/British and he's never seen this format. Is this an ISO thing or a SQL Server thing?

    08 Mart 2012 Perşembe 02:28
  • SQL-Server has two general date formats: mdy and dmy.  For the dmy, the date is interpreted as ydm when the year is first.  The default format for the date depends on the setting of each individual login; so two people can have a different date format when connecting to the same database.

    Some date formats are independant of the general date format.  For example, 20120201 is independant and will always be interpreted the same while 2012-02-01 is not.

    Use an independant format or set the general date format appropriately or use the Convert function with an appropriate value for the format.

    08 Mart 2012 Perşembe 03:02
  • Sorry to be a pain, but this doesn't answer my question.

     Why is British English in YYYYDDMM format?

    08 Mart 2012 Perşembe 03:48
  • Sorry to be a pain, but this doesn't answer my question.
     Why is British English in YYYYDDMM format?**

    I've just told you that the format dmy is interpreted as ydm (or yyyyddmm) and not as ymd when the year come first and British English use the dmy format; therefore, it uses the yyyyddmm format and not the yyyymmdd format.

    08 Mart 2012 Perşembe 04:12
  • Why is "the format dmy is interpreted as ydm (or yyyyddmm) and not as ymd"?
    08 Mart 2012 Perşembe 04:27
  • Why is "the format dmy is interpreted as ydm (or yyyyddmm) and not as ymd"?

    I have no idea but it's considered to be one of the biggest nuisances of SQL-Server. Since SQL-Server 2008, this has been changed for datetime2, datetimeoffset and date:

    Set dateformat dmy;
    Select cast ('2012-02-01' as datetime), cast ('2012-02-01' as datetime2),
    cast ('2012-02-01' as date);

    08 Mart 2012 Perşembe 04:58
  • Interesting thank you. I wonder what people of think of this inconsistency with DATETIME vs DATETIME2? Could be dangerous.

    I'd also be interested to hear from others if they know the reason for this madness. I'm surprised that I've been working on MS technologies for 10 years and never seen (or worse, noticed) this problem here in Australia where we might often pick the "correct" English.


    08 Mart 2012 Perşembe 05:03
  • Interesting thank you. I wonder what people of think of this inconsistency with DATETIME vs DATETIME2? Could be dangerous.

    It's a five year old discussion now - if we take into account the fact that SQL-Server 2008 begun public testing in 2007 - so nobody is interested in discussing about that anymore.  My suggestion would be to use the newer type datetime2 (or one of the other two new variants) exclusively wherever it's possible and to keep the older type datetime only for legacy systems.  Not only it doesn't suffer the parsing problem for the international format but for the same amount of space, it covers a bigger range for the years and it's more precise for the time portion.

    Some people keeps using the smalldatetime type for its space saving usefulness but today, the size of a database is rarely a problem.

    I'd also be interested to hear from others if they know the reason for this madness. I'm surprised that I've been working on MS technologies for 10 years and never seen (or worse, noticed) this problem here in Australia where we might often pick the "correct" English.

    If you think you know everything after 10 years of experience, you're not at the end of your surprises.

    08 Mart 2012 Perşembe 08:11
  • I was surprised at the lack of information about the problem. Perhaps I wasn't googling the right thing.

    Still I look forward to hearing an explanation for DMY --> YDM.

    08 Mart 2012 Perşembe 12:15
  • You should never depend on an implicit conversion of a date string to a datetime type.  There are far to many variables and the implicit conversion makes it best guess.  You should always use the CONVERT function to convert string.

    SET @x = CONVERT(datetime,'2012-02-01',120)

    • Yanıt Olarak Öneren Peja Tao 09 Mart 2012 Cuma 03:27
    • Yanıt Olarak İşaretleyen Peja Tao 15 Mart 2012 Perşembe 07:08
    08 Mart 2012 Perşembe 13:32
  • Unfortunately it's not the answer to my question - one that will likely never be answered.
    15 Mart 2012 Perşembe 22:25
  • You are confusing several different concepts.

    The code:

    DECLARE @x datetime
    SET @x = '2012-02-01' -- Should be February 1st
    

    Uses an internal algorithm, including the current date format and language setting, to attempt to determine how to implicitly convert the string to a datetime field.  It is not always correct.

    The 2nd is:

    SELECT @x

    Displays the date in the current language and date format defined by the client side. 

    Without knowing the settings of your language and date format at the time of running the process, it is impossible to guess what you are seeing.   Just because this returns to the screen

    2012-01-02 00:00:00.000

    does not mean Jan 2, 2012, if your date format is set to ydm.

    The bottom line is, you can never guarantee an implicit conversion of string to datetime will work every time.  You should always use the CONVERT function with the correct format code.

    Please see this document:

    http://msdn.microsoft.com/en-us/library/ms180878.aspx#StringLiteralDateandTimeFormats

    16 Mart 2012 Cuma 19:50
  • The 2nd is:

    SELECT @x

    Displays the date in the current language and date format defined by the client side. 

    Without knowing the settings of your language and date format at the time of running the process, it is impossible to guess what you are seeing.   Just because this returns to the screen

    2012-01-02 00:00:00.000

    does not mean Jan 2, 2012, if your date format is set to ydm.

    The date format (ydm and the like) is never used for writing, only for parsing when reading a string of chars.

    Also, when you write "select @x" when @x is a date(-time), the conversion of @x to a string of characters is not done on the server side but on the client side; therefore, it is totally independant of any setting on the SQL-Server like the current language.  SQL-Server return a value of date(-time) type to the client and it's up to the later to display it in any format that it might chose.

    For example, in the following Select statement, the second field is converted by SQL-Server to a string of chars while the first one is returned as is (ie., as a datetime type and not as a string type) without any conversion at all; therefore, it's quite possibly that you will see two different strings for the first and the second column:

    Select @x, cast (@x as varchar(20))

    or:

    Select getdate(), cast (getdate() as varchar(20))
    16 Mart 2012 Cuma 22:30
  • To just prove what Tom Philips said below, on my machine I got a different result.

    You cannot tell from that output what date it is unless you check and you are sure about both the default language of your SQL instance and of the user you are logged in as. In my case they are both british-english. You are saying that they appear to be british-english. Are they?


    Beginner



    27 Mart 2012 Salı 20:47