Convert mixed format string datetime value to datetime datatype-CodePudding

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?

CodePudding user response：

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.