Convert a Custom (String) Date Format to datetime in SQL-CodePudding

I need to get all rows from a table that have a date of the last 7 days or greater. My issue is that when the DB was originally setup, someone set it up as VARCHAR. So now I need to CONVERT the String to a DateTime.

The issue is, the format of the Date/Time isn't recognized by SQL. The format is:

2023-01-01T00:00:00.000 0000

If I can trim off the last 8 characters from the string, SQL will recognize it. But I've had no luck thus far. The statement I was attempting was:

SELECT CONVERT(datetime, TRIM('.000 0000' FROM date_col), 127) FROM table_name;

But that resulted in the error:

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

CodePudding user response：

Try this

SELECT CAST(LEFT(REPLACE('2023-01-01T00:00:00.000 0000', 'T', ' '), 19) AS DATETIME)

CodePudding user response：

No need for the replace with datetime2(n)

Select WithMS = try_convert(datetime2(3),left('2023-01-01T00:00:00.100 0000',23))
      ,SansMS = try_convert(datetime2(0),left('2023-01-01T00:00:00.100 0000',23))

Results

WithMS                    SansMS
2023-01-01 00:00:00.100   2023-01-01 00:00:00