how to convert this varchar to datetime format?

Question

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?

Answer 1

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)

Answer 2

DATETIME only allows three places of millisecond precision. You'll either need to trim the trailing places beyond the first three milliseconds or if you're using any version of SQL Server 2008 or later you can use DATETIME2.

DATETIME2 allows up to seven places of millisecond precision and the precision level can be explicitly set for your table or query. Further details and differences can be found at datetime2 (Transact-SQL).

You can verify by executing the following CAST query:

SELECT CAST('2015-12-02 20:40:37.8130000' AS DATETIME2)

Answer 3

When you have a specific date format in your varchar column, you can tell CONVERT what that format is in order to get a correct conversion, independently of language or regional settings.

In your case,

SELECT CONVERT(datetime2(3), start_time, 121) FROM track_date;

Note: datetime will be deprecated and replaced by datetime2(n) where n is the number of decimals of the seconds.

See also: CAST and CONVERT (Transact-SQL)