Convert varchar to datetime in sql which is having millisec

Question

I have a column abc varchar(100) with data like 2011-09-26 16:36:57.810000

I want to convert this column to DATETIME...

But doing a

Convert(DATETIME, abc,120) 

is giving this error:

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

Can any one please help me convert my varchar format to datetime in SQL Server 2008?

Thanks in advance

Answer 1

You can use style 121 but you can have only 3 digits for milliseconds (i.e yyyy-mm-dd hh:mi:ss.mmm(24h)) format.

declare @abc varchar(100)='2011-09-26 16:36:57.810' 
select convert(datetime,@abc,121)

So you can sort it out by limiting the varchar field to 23 characters before converting as:

declare @abc varchar(100)='2011-09-26 16:36:57.810000' 
select convert(datetime,convert(varchar(23),@abc),121)

Or use the Left() function to get first 23 characters as:

select convert(datetime,left(@abc,23),121)

Try to avoid storing date as string.

Answer 2

In case you need 6 digits precision use DATETIME2

SELECT CONVERT(DATETIME2, '2016-08-09T08:08:50.358000', 126) as MSSQLDateTime2
SELECT CONVERT(DATETIME, '2016-08-09T08:08:50.358', 126) as MSSQLDateTime