为什么SQL Server会误解此ISO 8601格式日期？ [英] Why is SQL Server misinterpreting this ISO 8601 format date?

问题描述

为什么SQL Server（2005）会误解此ISO 8601格式日期？ （YYYY-MM-DD）

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

文本格式的日期显然是1月5日，但由于某种原因，SQL Server解释为5月1日。世界上没有YYYY-DD-MM的日期格式，为什么会发生这种情况？我使用这种格式已有多年了，以前从未遇到过问题，因此不确定这种情况有什么不同。

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.

即使我使用CONVERT将其强制进入ISO8601，它仍然会出错：

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

编辑：糟糕-我在使用'CONVERT（VARCHAR真的是说CONVERT（DATETIME），所以这没什么作用，谢谢@RBarryYoung

但是，如果我运行上述两个示例之一在另一台服务器上（SQL 2012），它们都正确打印了'Jan 5 2013 12:00 AM'

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'

这是怎么回事？我认为使用ISO格式的主要原因之一

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?

推荐答案

对于SQL Server，SQL Server是否可以使月份和日期变得清晰？对于新的数据类型（ date / datetime2 ）

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

为了向后兼容，这仍然是dateformat取决于 datetime 。

For backward compatibility this still is dateformat dependent for datetime.

在SQL Server 2012上

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*/

处理这些数据类型时，可以使用 yyyymmdd 作为明确的格式。

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)

这篇关于为什么SQL Server会误解此ISO 8601格式日期？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！