I have a table with 1M rows and a CREATED_AT varchar column. The formatting of the date string in this column:
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.
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;
And to demonstrate why using the 120 style number is important here, see this db<>fiddle.