插入日期时间时从字符串转换日期和/或时间时转换失败 [英] Conversion failed when converting date and/or time from character string while inserting datetime

问题描述

我试图按如下方式创建一个表，

I was trying to create a table as follows,

create table table1(date1 datetime,date2 datetime);

首先我尝试插入如下值，

First I tried inserting values as below,

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

它给出了错误提示，

无法将 varchar 转换为日期时间

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

Then I tried below format as one of the post suggested by our 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));

但我仍然收到错误消息，

But am still getting the error saying,

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

有什么建议吗?

推荐答案

SQL Server 支持多种格式 - 请参阅 有关 CAST 和 CONVERT 的 MSDN 联机丛书.大多数这些格式取决于您的设置 - 因此，这些设置有时可能有效 - 有时则无效.

There are many formats supported by SQL Server - see the MSDN Books Online on CAST and CONVERT. Most of those formats are dependent on what settings you have - therefore, these settings might work some times - and sometimes not.

解决此问题的方法是使用 SQL Server 支持的(稍作调整的)ISO-8601 日期格式 - 此格式始终 - 无论您使用什么SQL Server 语言和日期格式设置.

The way to solve this is to use the (slightly adapted) ISO-8601 date format that is supported by SQL Server - this format works always - regardless of your SQL Server language and dateformat settings.

SQL Server 支持 ISO-8601 格式两种口味:

The ISO-8601 format is supported by SQL Server comes in two flavors:

• YYYYMMDD 仅用于日期(无时间部分)；请注意:没有破折号！，这非常重要！YYYY-MM-DD不独立于 SQL Server 中的日期格式设置，并且不适用于所有情况！

• YYYYMMDD for just dates (no time portion); note here: no dashes!, that's very important! YYYY-MM-DD is NOT independent of the dateformat settings in your SQL Server and will NOT work in all situations!

或:

• YYYY-MM-DDTHH:MM:SS 用于日期和时间 - 请注意:这种格式有破折号(但它们可以省略)，以及一个固定的 T 作为 DATETIME 的日期和时间部分之间的分隔符.

• YYYY-MM-DDTHH:MM:SS for dates and times - note here: this format has dashes (but they can be omitted), and a fixed T as delimiter between the date and time portion of your DATETIME.

这对 SQL Server 2000 及更新版本有效.

This is valid for SQL Server 2000 and newer.

因此在您的特定情况下 - 使用这些字符串:

So in your specific case - use these strings:

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

您应该没问题(注意:为此，您需要使用国际24 小时格式，而不是上午/下午 12 小时格式).

and you should be fine (note: you need to use the international 24-hour format rather than 12-hour AM/PM format for this).

或者:如果您使用的是 SQL Server 2008 或更新版本，您还可以使用 DATETIME2 数据类型(而不是普通的 DATETIME) 和您当前的 INSERT 可以正常工作！:-) DATETIME2 在转换上要好得多，而且不那么挑剔 - 无论如何，它是 SQL Server 2008 或更高版本的推荐日期/时间数据类型.

Alternatively: if you're on SQL Server 2008 or newer, you could also use the DATETIME2 datatype (instead of plain DATETIME) and your current INSERT would just work without any problems! :-) DATETIME2 is a lot better and a lot less picky on conversions - and it's the recommend date/time data types for SQL Server 2008 or newer anyway.

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 中的任何语言和日期格式设置.

Don't ask me why this whole topic is so tricky and somewhat confusing - that's just the way it is. But with the YYYYMMDD format, you should be fine for any version of SQL Server and for any language and dateformat setting in your SQL Server.

这篇关于插入日期时间时从字符串转换日期和/或时间时转换失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！