CODEWITHSUNDEEP
sqlsql-servert-sqldatetime
NickG
NickG

Reputation: 9802

Why is SQL Server misinterpreting this ISO 8601 format date?

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?

Upvotes: 2

Views: 3143

Answers (1)

Martin Smith
Martin Smith

Reputation: 452977

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)

Upvotes: 6

Related Questions