在SQL Server中将varchar（50）转换为datetime数据类型

I'm trying to update varchar(50) field to datetime in SQL Server, tried couple of ways but no success.

我正在尝试在SQL Server中将varchar(50)字段更新为datetime,尝试了几种方法但没有成功。

I ways get error

我的方式得到错误

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

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

Here is sample values in the OrderDateTime column:

以下是OrderDateTime列中的示例值:

2014-05-21 04:21:45.3346898
2014-02-26 08:43:58.5693805
2014-04-29 15:57:21.0778236
2014-04-09 07:23:57.3886545
2014-03-14 16:03:46.3919145
:::::::::::::::::::::::::::

Please help me either to identify that row causing issue or let me know if any force convert to datetime way available.

请帮助我确定导致问题的那一行,或者让我知道是否有任何力量转换为datetime方式可用。

I tried these:

我试过这些:

UPDATE  Orders SET OrderDateTime=CONVERT(datetime, OrderDateTime)
UPDATE  Orders SET OrderDateTime=CAST(OrderDateTime AS datetime)

2 个解决方案

#1

Try this

UPDATE  Orders SET OrderDateTime=CAST(OrderDateTime AS datetime2)

Note :if you're on SQL Server 2008 or newer, you could use the DATETIME2 datatype instead of plain DATETIME.

注意:如果您使用的是SQL Server 2008或更高版本,则可以使用DATETIME2数据类型而不是纯DATETIME。

datetime2 can be considered as an extension of the existing datetime type that has a larger date range, a larger default fractional precision, and optional user-specified precision.

datetime2可以视为现有日期时间类型的扩展,它具有更大的日期范围,更大的默认小数精度和可选的用户指定精度。

#2

Try this...

ODBC canonical (with milliseconds).
------------------------------------

SELECT CONVERT(DATETIME, '2013-05-04 14:14:35.073', 21)

SELECT CONVERT(DATETIME, '2013-05-04 14:14:35.073', 121)

ISO8601.
--------
SELECT CONVERT(DATETIME, '2013-05-04T14:14:35.073', 126) -- 2013-05-04 14:14:35.073 

SELECT CONVERT(VARCHAR, GETDATE(), 127) -- 2013-05-04T14:14:35.073
SELECT CONVERT(DATETIME, '2013-05-04T14:14:35.073', 127) -- 2013-05-04 14:14:35.073

Reference Url: http://www.codeproject.com/Articles/576178/cast-convert-format-try-parse-date-and-time-sql#2_3

参考文献:http://www.codeproject.com/Articles/576178/cast-convert-format-try-parse-date-and-time-sql#2_3

#1