Tim Biegeleisen
2020-11-29 18:41:48
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');