插入datetime时从字符串转换日期和/或时间时转换失败

Question

我试图创建一个表，如下所示，

create table table1(date1 datetime,date2 datetime);

首先，我尝试如下插入值，

insert into table1 values('21-02-2012 6:10:00 PM','01-01-2001 12:00:00 AM');

它给出了错误的说法，

无法将varchar转换为日期时间

然后，我尝试使用以下格式作为我们stackoverflow建议的帖子之一，

insert into table1 values(convert(datetime,'21-02-2012 6:10:00 PM',5)
                          ,convert(datetime,'01-01-2001 12:00:00 AM',5));

但是我仍然收到错误消息，

从字符串转换日期和/或时间时转换失败

有什么建议？

Answer 1

SQL Server支持多种格式-有关CAST和CONVERT的信息，请参见MSDN联机丛书。这些格式中的大多数 取决于 您所拥有的设置-因此，这些设置有时可能会起作用-有时不起作用。

解决此问题的方法是使用SQL Server支持的（略微调整的） ISO-8601日期格式 -这种格式 始终有效 -不管您使用的SQL
Server语言和dateformat设置如何。

在ISO-8601格式由SQL
Server支持有两种形式：

• YYYYMMDD只是日期（无时间部分）；注意： 没有破折号！ ，那非常重要！YYYY-MM-DD是 不是 独立于你的SQL Server的日期格式设置，将 不 适用于所有情况！

或者：

• YYYY-MM-DDTHH:MM:SS日期和时间-请在此处注意：此格式 带有 破折号（但 可以 省略），并T在的日期和时间部分之间固定为定界符DATETIME。

这对SQL Server 2000及更高版本有效。

因此，在您的具体情况下-使用以下字符串：

insert into table1 values('2012-02-21T18:10:00', '2012-01-01T00:00:00');

并且应该没问题（请注意：为此，您需要使用国际 24小时制 而不是12小时AM / PM格式）。

或者 ：如果您使用的是SQL Server 2008 或更高版本，则也可以使用DATETIME2数据类型（而不是plain
DATETIME），并且当前数据类型INSERT将正常工作而不会出现任何问题！:-)DATETIME2更好，对转换的挑剔也少得多-
无论如何，它还是SQL Server 2008或更高版本的推荐日期/时间数据类型。

SELECT
   CAST('02-21-2012 6:10:00 PM' AS DATETIME2),     -- works just fine
   CAST('01-01-2012 12:00:00 AM' AS DATETIME2)   -- works just fine

不要问我为什么整个主题如此棘手且有些令人困惑-这就是事实。但是使用该YYYYMMDD格式时，对于SQL Server的任何版本以及SQL
Server中的任何语言和dateformat设置，都应该没问题。