how to convert this varchar to datetime format?

All we need is an easy explanation of the problem, so here it is.

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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)

Method 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)

Method 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)

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply