Best way to create new DateTime column from VARCHAR column-CodePudding

I have a table with 1M rows and a CREATED_AT varchar column. The formatting of the date string in this column:

2021-10-13 05:03:42.638 00
2021-10-18 21:28:49.98 00
2021-12-08 02:09:03.17 00

I want to cast this string into a new datetime column (called CREATED_DT).

Attempts

select 
    cast([CREATED_AT] as datetime) 
from
    [mydb].[dbo].[mytable]

Error:

Conversion failed when converting date and/or time from character string

select 
    convert(datetime, [CREATED_AT]) 
from 
    [mydb].[dbo].[mytable]

Conversion failed when converting date and/or time from character string

select 
    try_convert(DateTime, [CREATED_AT]) 
from
    [mydb].[dbo].[mytable]

Query executes, but returns all nulls.

select 
    try_convert(DateTime, [CREATED_AT], 108) 
from
    [mydb].[dbo].[mytable]

Query executes, but returns all nulls.

select 
    try_cast([CREATED_AT] as datetime) 
from
    [mydb].[dbo].[mytable]

Query executes, but returns all nulls.

CodePudding user response：

Assuming you never have to care about the useless 00 at the end (either it's always 00 or you don't care if it's ever something else), and you're ok losing a tiny bit of precision, you can take the left 22 characters and try to convert the values with a safe style number (in your case, 120):

DECLARE @d table(CREATED_AT varchar(32));

INSERT @d(CREATED_AT) VALUES
('2021-10-13 05:03:42.638 00'),
('2021-10-18 21:28:49.98 00'),
('2021-12-08 02:09:03.17 00');

SELECT CREATED_AT, 
  as_datetime = TRY_CONVERT(datetime, LEFT(CREATED_AT, 22), 120) 
FROM @d;

If you don't want to lose the precision (you can't keep .638 as datetime, anyway, for example), or if some value might have 1 or 0 decimal places, or some values might not contain the xx at all, you can do something similar but use some string tricks to truncate the value where the appears (and also handles if it doesn't):

SELECT CREATED_AT,
  as_datetime2 = TRY_CONVERT(datetime2(3), 
    LEFT(CREATED_AT, COALESCE(NULLIF(CHARINDEX(' ', 
    CREATED_AT), 0), 32)-1), 120) 
FROM @d;

Example db<>fiddle

And to demonstrate why using the 120 style number is important here, see this db<>fiddle.

CodePudding user response：

The style you are looking for is 127, but your issue is that you only include the hour for the time-zone, you also need minutes. So just add :00 to each value.

You can then convert to a regular datetime from there, by using SWITCHOFFSET

DECLARE @v varchar(30) = '2021-10-13 05:03:42.638 00';

SELECT TRY_CONVERT(datetimeoffset, @v   ':00', 127);

SELECT CONVERT(datetime, SWITCHOFFSET(TRY_CONVERT(datetimeoffset, @v   ':00', 127), 0));

db<>fiddle