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

Conversion failed when converting date and/or time from character string while inserting datetime

提问by Mari

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');

It has given error saying,

它给出了错误说，

Cannot convert varchar to datetime

无法将 varchar 转换为日期时间

Then I tried below format as one of the post suggested by our stackoverflow,

然后我尝试了以下格式作为我们的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,

但我仍然收到错误消息，

Conversion failed when converting date and/or time from character string

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

Any suggestions?

有什么建议？

采纳答案by marc_s

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

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

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

解决此问题的方法是使用SQL Server 支持的（稍作调整的）ISO-8601 日期格式- 此格式始终有效- 无论您的 SQL Server 语言和日期格式设置如何。

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

SQL Server 支持的ISO-8601 格式有两种：

• YYYYMMDDfor just dates (no time portion); note here: no dashes!, that's very important! YYYY-MM-DDis NOTindependent of the dateformat settings in your SQL Server and will NOTwork in all situations!

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

or:

或者：

• YYYY-MM-DDTHH:MM:SSfor dates and times - note here: this format hasdashes (but they canbe omitted), and a fixed Tas delimiter between the date and time portion of your DATETIME.

• YYYY-MM-DDTHH:MM:SS对于日期和时间 - 请注意：此格式有破折号（但可以省略），并且固定T为DATETIME.

This is valid for SQL Server 2000 and newer.

这对 SQL Server 2000 和更新版本有效。

So in your concrete case - use these strings:

所以在你的具体情况下 - 使用这些字符串：

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

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

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

Alternatively: if you're on SQL Server 2008or newer, you could also use the DATETIME2datatype (instead of plain DATETIME) and your current INSERTwould just work without any problems! :-) DATETIME2is 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.

或者：如果您使用的是 SQL Server 2008或更新版本，您也可以使用DATETIME2数据类型（而不是普通的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  

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

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

回答by Ashraf Abusada

The conversion in SQL server fails sometimes not because of the Date or Time formats used, It is Merely because you are trying to store wrong data that is not acceptable to the system.

SQL server 中的转换失败有时不是因为使用的日期或时间格式，而仅仅是因为您试图存储系统不可接受的错误数据。

Example:

例子：

Create Table MyTable (MyDate);

Create Table MyTable (MyDate);

Insert Into MyTable(MyDate) Values ('2015-02-29');

Insert Into MyTable(MyDate) Values ('2015-02-29');

The SQL server will throw the following error:

SQL 服务器将抛出以下错误：

Conversion failed when converting date and/or time from character string.

Conversion failed when converting date and/or time from character string.

The reason for this error is simply there is no such date (Feb-29) in Year (2015).

出现此错误的原因很简单，即年（2015 年）中没有这样的日期（2 月 29 日）。

回答by Raj

Simple answer - 5 is Italian "yy" and 105 is Italian "yyyy". Therefore:

简单的答案 - 5 是意大利语“yy”，105 是意大利语“yyyy”。所以：

SELECT convert(datetime,'21-02-12 6:10:00 PM',5)

will work correctly, but

会正常工作，但是

SELECT convert(datetime,'21-02-12 6:10:00 PM',105)

will give error.

会报错。

Likewise,

同样地，

SELECT convert(datetime,'21-02-2012 6:10:00 PM',5)

will give error, where as

会给出错误，如

SELECT convert(datetime,'21-02-2012 6:10:00 PM',105)

will work.

将工作。

回答by Shnugo

Whenever possible one should avoid culture specific date/time literals.

只要有可能，就应该避免使用文化特定的日期/时间文字。

There are some secureformats to provide a date/time as literal:

有一些安全格式可以将日期/时间作为文字提供：

All examples for 2016-09-15 17:30:00

所有示例 2016-09-15 17:30:00

ODBC (my favourite, as it is handled as the realtype immediately)

ODBC（我最喜欢的，因为它被立即作为真正的类型处理）

• {ts'2016-09-15 17:30:00'}--Time Stamp
• {d'2016-09-15'}--Date only
• {t'17:30:00'}--Time only

• {ts'2016-09-15 17:30:00'}--时间戳
• {d'2016-09-15'}--仅限日期
• {t'17:30:00'}--仅限时间

ISO8601 (the best for everywhere)

ISO8601（最好的无处不在）

• '2016-09-15T17:30:00'--be aware of the Tin the middle!

• '2016-09-15T17:30:00'——注意T中间！

Unseperated (tiny risk to get misinterpreted as number)

未分离（被误解为数字的微小风险）

• '20160915'--only for puredate

• '20160915'--仅适用于纯日期

Good to keep in mind: Invalid dates tend to show up with strange errors

请记住：无效日期往往会出现奇怪的错误

• There is no 31st of June or 30th of February...

• 没有6月31日或2月30日...

One more reason for strange conversion errors: Order of execution!

出现奇怪转换错误的另一个原因：执行顺序！

SQL-Server is well know to do things in an order of execution one might not have expected. Your written statement looks like the conversion is done beforesome type related action takes place, but the engine decides - why ever - to do the conversion in a later step.

众所周知，SQL-Server 以人们可能没有预料到的执行顺序来做事。您的书面声明看起来像是在某些类型相关的操作发生之前完成了转换，但引擎决定 - 为什么永远 - 在稍后的步骤中进行转换。

Here is a great article explaining this with examples: Rusano.com: "t-sql-functions-do-no-imply-a-certain-order-of-execution"and here is the related question.

这是一篇用示例解释这一点的精彩文章：Rusano.com：“t-sql-functions-do-no-imply-a-certain-order-of-execution”，这是相关问题。

回答by Pronab Roy

Just update the date format as like bellow

只需更新日期格式，如下所示

yyyy-MM-dd hh:MM:ss

It solves the problem for me and it works fine

它为我解决了问题并且工作正常

回答by Ahmed Soliman Flasha

the best way is this code

最好的方法是这段代码

"select * from [table_1] where date between convert(date,'" + dateTimePicker1.Text + "',105) and convert(date,'" + dateTimePicker2.Text + "',105)"

回答by ARLE ANDINO

convert(datetime2,((SUBSTRING( ISNULL(S2.FechaReal,e.ETA),7,4)+'-'+ SUBSTRING( ISNULL(S2.FechaReal,e.ETA),4,2)+'-'+ SUBSTRING( ISNULL(S2.FechaReal,e.ETA),1,2) + ' 12:00:00.127')))  as fecha,

回答by SwR

Please Try this.

请试试这个。

SQL Server expects dates in MM/DD/YYYY format,If English is set as your default language.Here am saving datepicker value to sql2008 database.My field type is datetime in database.dpdob is my datepicker name.

SQL Server 需要 MM/DD/YYYY 格式的日期，如果英语设置为您的默认语言。这里我将日期选择器值保存到 sql2008 数据库中。我的字段类型是数据库中的日期时间。dpdob 是我的日期选择器名称。

           Dim test = dpdob.Text.Replace("-", "/")
           Dim parts As String() = test.Split(New Char() {"/"c})
           Dim firstPart As String = parts(0)
           Dim thirdPart As String = parts(2)
           Dim secondPart As String = parts(1)
           Dim test1 = secondPart + "/" + firstPart + "/" + thirdPart
           Dim dob = test1

Now use dob in your insert query.

现在在插入查询中使用 dob。

回答by Biddut

You can try this code

你可以试试这个代码

select (Convert(Date, '2018-04-01'))

回答by vapcguy

I had this issue when trying to concatenate getdate()into a string that I was inserting into an nvarchar field.

我在尝试连接getdate()到我插入 nvarchar 字段的字符串时遇到了这个问题。

I did some casting to get around it:

我做了一些铸造来解决它：

 INSERT INTO [SYSTEM_TABLE] ([SYSTEM_PROP_TAG],[SYSTEM_PROP_VAL]) VALUES 
   (
    'EMAIL_HEADER',
    '<h2>111 Any St.<br />Anywhere, ST 11111</h2><br />' + 
        CAST(CAST(getdate() AS datetime2) AS nvarchar) + 
    '<br /><br /><br />'
   )

That's a sanitized example. The key portion of that is:

这是一个经过消毒的例子。其中的关键部分是：

...' + CAST(CAST(getdate() AS datetime2) AS nvarchar) + '...

...' + CAST(CAST(getdate() AS datetime2) AS nvarchar) + '...

Casted the date as datetime2, then as nvarcharto concatenate it.

将日期转换为datetime2，然后将nvarchar其连接起来。