CODEWITHSUNDEEP
sql-servert-sqldatetimeiso8601
navy
navy

Reputation: 169

Fix datetime string in SQL Server

I have a table with 43 000 000 rows. In datetime column data looks 2020.04.22T04:39:29.359 and it's of type VARCHAR.

It looks like an ISO format but there are ., and for ISO I need -. What is the best way to convert the values in these rows to datetime?

One of my variant was subst . to -:

UPDATE table 
SET [Column 3] = REPLACE([Column 3], '.', '-');

but then I need to cut a microseconds from the end.

How to do this cut?

Or maybe you can advice more truish way.

Upvotes: 1

Views: 256

Answers (2)

mandy1339
mandy1339

Reputation: 516

If every single row in the table has the date that follows the format 2020.04.22T04:39:29.359 you can do the following update statement:

UPDATE table 

SET [Column 3] = SUBSTRING([Column 3],1,4) + '-' + SUBSTRING([Column 3],6,2) + '-' + SUBSTRING([Column 3],9,15)

To only fix the 5th and 8th character without affecting the "." character from the microseconds. After that you should be able to do the conversion to datetime.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

You may use TRY_CONVERT here, after first doing a bit of massaging to bring your raw datetime strings into a format which SQL Server recognizes:

UPDATE yourTable
SET new_dt_col = TRY_CONVERT(
                     datetime,
                     REPLACE(LEFT(dt_col, 19), '.', '-') + '.' + RIGHT(dt_col, 3)
                 );

To be explicit, the replacement logic used above would first convert this:

2020.04.22T04:39:29.359

into this:

2020-04-22T04:39:29.359

You may verify for yourself that the following conversion works correctly:

SELECT TRY_CONVERT(datetime, '2020-04-22T04:39:29.359');

Upvotes: 3

Related Questions