Convert mixed format string datetime value to datetime datatype

Question

SQL Server 2019 is receiving data from upstream PostgreSQL.

The four date columns are defined as datatype Varchar(50) and hold datetime values like this:

2021-12-09 09:16:09+00
2021-12-15T02:40:39+01:00
2021-12-15 1:27:56
2021-12-15 0:45:00

How can I convert this from varchar to proper datetime datatype and insert to another table in SQL Server 2019?

I suggest fixing your data at its source and getting it into a single common format. — Tim Biegeleisen, Aug 27 '22 at 04:35
@TimBiegeleisen: Thanks, but Upstream source is out of scope for us. We are authorised only to modify downstream data. — Aryan, Aug 27 '22 at 04:46

score 1 · Accepted Answer · answered Aug 27 '22 at 04:53

1

You could try using the base string functions along with TRY_CONVERT() here:

SELECT dt,
       TRY_CONVERT(datetime, SUBSTRING(dt, 1, 10) + ' ' + SUBSTRING(dt, 12, 8)) AS dt_out
FROM yourTable;

Here is a working demo.

answered Aug 27 '22 at 04:53

Tim Biegeleisen

1 Answers1