2

Why is SQL Server (2005) misinterpreting this ISO 8601 format date? (YYYY-MM-DD)

DECLARE @FromDate DATETIME
SET @FromDate = '2013-01-05'
PRINT @FromDate
-- Prints: May  1 2013 12:00AM

The date in text format, is clearly the 5th of January but for some reason SQL Server is interpreting as the 1st of may. There is no date format in the world which is YYYY-DD-MM so why is this happening? I've been using this format for years and never had a problem before so I'm not sure what's different in this instance.

Even if I force it into ISO8601 using CONVERT, it still gets it wrong:

DECLARE @FromDate DATETIME
SET @FromDate = CONVERT(VARCHAR, '2013-01-05', 126) 
PRINT @FromDate
-- Still prints: May  1 2013 12:00AM

EDIT: Oops - I'm using 'CONVERT(VARCHAR above where I really mean CONVERT(DATETIME), so that's why that wasn't taking any effect. Thanks @RBarryYoung

However if I run either of the two examples above on a different server (SQL 2012) they both correctly print 'Jan 5 2013 12:00AM'

What's happening here? I thought one of the main reasons to use ISO format with SQL Server was that it made the month and day unambiguous?

NickG
  • 9,315
  • 16
  • 75
  • 115
  • 3
    Actually, `YYYY-DD-MM` is the date format in French locale (see this [Connect](http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=290971) page attempting to get it depreciated!) Try `YYYYMMDD` format for your literals - I thoroughly recommend [this article](https://sqlblog.org/2009/10/16/bad-habits-to-kick-mis-handling-date-range-queries) by MVP Aaron Bertrand on some background of the subject. – Bridge Oct 24 '13 at 12:03
  • Thanks @Bridge - that's very useful (but my server is not in French language, so I'm still confused!) – NickG Oct 24 '13 at 12:21
  • If specifying a date in a SQL string I always use `'5 Jan 2013'` format - it will parse that and it's definitely not ambiguous. – Charleh Oct 24 '13 at 12:24
  • 1
    @Charleh `SET LANGUAGE Italian; SELECT CAST('5 Jan 2013' AS DATETIME)` gives `Conversione non riuscita durante la conversione di una stringa di caratteri in una data o ora.` – Martin Smith Oct 24 '13 at 12:26
  • 1
    Don't set the language to Italian then! – Charleh Oct 24 '13 at 12:29
  • @Charleh That's rarely practical. Very often I only have a numeric date available – NickG Oct 24 '13 at 12:33
  • I'm not saying do this in a sproc or for variable, I'm saying for ad-hoc queries where you need to specify a date or date range it's less ambiguous to specify a date in your language with a textual month. Why everyone doesn't use d/m/y instead of m/d/y I don't know, but as we call it in the office it's the `wonderful worldq of dates`. – Charleh Oct 24 '13 at 12:36

1 Answers1

6

It only makes it unambiguous for the newer datatypes (date/datetime2)

For backward compatibility this still is dateformat dependent for datetime.

On SQL Server 2012

SET DATEFORMAT DMY

SELECT CAST('2013-01-05' AS DATETIME),   /*May*/
       CAST('2013-01-05' AS DATETIME2),  /*Jan*/
       CAST('20130105' AS DATETIME),     /*Jan*/
       CAST('20130105' AS DATETIME2)     /*Jan*/

You can use yyyymmdd as an unambiguous format when dealing with those datatypes.

See The ultimate guide to the datetime datatypes (this is referred to as the unseparated format in that article)

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • But that article agrees with what I'm saying and doesn't explain my problem. In particular it says: "these new types are language neutral for a separated datetime literal as long as the year comes first. SQL Server need to determine that this is the year part, so it requires 4 numbers year (yyyy, not yy). If that is the case, then the string will be interpreted as year first, then month and finally day - regardless of DATEFORMAT or language setting." – NickG Oct 24 '13 at 12:07
  • @NickG - Please read the first three words of that quote. You are not using a new type. – Martin Smith Oct 24 '13 at 12:10
  • OK, I can see what you mean about the unseparated format, but I still don't understand why even when I *FORCE* it into ISO 8601 using convert, it still gets it wrong. ISO 8601 uses hyphens. – NickG Oct 24 '13 at 12:11
  • Because that's just the way it works with the old datatypes. This format is only non dateformat dependant with the new datatypes. Probably goes back to Sybase days more than 20 years ago. – Martin Smith Oct 24 '13 at 12:13
  • 2
    @NickG - From SQL Server 2008 the situation is fixed. The older dataypes and behaviour are maintained for backward compatibility and they recommend the newer dataypes for new work. For previous versions you'll just have to accept that it doesn't work as you want and use a format that is neutral. I'm just telling you how it is so I'm not sure why you are complaining to me about it. – Martin Smith Oct 24 '13 at 12:20
  • 3
    @NickG Actually, the reason that your expression `CONVERT(VARCHAR, '2013-01-05', 126)` doesn't work is that you aren't converting a date to a string in the CONVERT(), you're converting a string to a string, so it doesn't actually change anything. Try it like `CONVERT(DATETIME, '2013-01-05', 126)` instead. I don't have time to test it right now, but I think that works because now it's converting a string to a datetime. – RBarryYoung Oct 24 '13 at 12:24
  • 1
    @MartinSmith Sorry - just frustrated :) The SQL documentation implies that using CONVERT to force ISO format is unambiguous, however as RBarryYoung points out, I perhaps had my syntax incorrect so it wasn't actually doing the convert. I will accept your answer :) – NickG Oct 24 '13 at 12:28
  • 1
    Thanks @RBarryYoung - I think that may have been a significant source of my confusion/frustration! – NickG Oct 24 '13 at 12:28
  • 1
    @NickG Catching my own typos is the hardest kind of bug for me to find. That's why it's always good to have an extra pair of eyes. – RBarryYoung Oct 24 '13 at 12:35
  • I came back to this answer twice before I realized that you simply use YYYYMMDD format instead of the ISO format to solve the problem: SET @FromDate = '20130105' -- don't use '2013-01-15' – machnine May 02 '19 at 09:30