Sql-server – how to convert this varchar to datetime format

sql servert-sqltype conversion

I have a varchar column has data like (2015-12-02 20:40:37.8130000) which i have imported from csv file to SQL table. i need to convert this column to datatime as (2013-03-17 14:15:49.687). 

 ALTER TABLE track_date alter column start_time datetime.

getting error as :

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

while converting using convert function , getting same error:

SELECT convert(datetime,start_time,5) from track_date

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

Can you please help, how to convert this varchar data to datetime format?

Best Answer

There is too much precision in the varchar to be converted into datetime.

One option(better in my opinion) would be to change the target column to datetime2(7). Then you can convert like this:

declare @dt varchar(50)
set @dt = '2015-12-02 20:40:37.8130000'

select cast(@dt as datetime2(7));

If changing the column is not an option the you can do the conversion like this: 

declare @dt varchar(50)
set @dt = '2015-12-02 20:40:37.8130000'

select cast(cast(@dt as datetime2(7))as datetime)
Related Question