Converting varchar(50) to datetime data type in SQL Server

Question

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

I ways get error

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

Here is sample values in the OrderDateTime column:

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.

I tried these:

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

Isn't this the same as - http://stackoverflow.com/questions/1509977/convert-varchar-into-datetime-in-sql-server ?? — Leptonator, Mar 09 '15 at 04:44
did you check with my answer ? let me know if you have still problems — Arunprasanth K V, Mar 09 '15 at 05:31

score 1 · Accepted Answer · edited Mar 09 '15 at 05:51

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.

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.

edited Mar 09 '15 at 05:51

shA.t

16,580
5
54
111

answered Mar 09 '15 at 05:07

Arunprasanth K V

20,733
8
41
71

great.. worked, but not sure my application will complain if I use datetime2, let me know your experience? – Abhimanyu Mar 09 '15 at 05:42
1

@AVatsa you can use that , just see the updated answer, it is just an extension of datetime – Arunprasanth K V Mar 09 '15 at 05:52

score 0 · Answer 2 · answered Mar 09 '15 at 04:48

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

Converting varchar(50) to datetime data type in SQL Server

2 Answers2