Varchar datetime with EDT in it convert into datetime

I have this nvarchar column to store the datetime values and the values in this format. I need to convert this to a datetime.

Mon 19 Aug 2024 16:19:38:154 EDT   

-> how do we get this into datetime only? Do not need EDT.

There are some rows where this value can be NULL.

SQL Server version: 2022

CAST(REPLACE(datetimefield1, 'EDT', '') AS DATETIME)

Conversion failed error

Sample data:

CREATE TABLE dbo.stringdates 
(
    MyDateValues nvarchar(200)
);

INSERT INTO dbo.stringdates (MyDateValues) 
VALUES ('Tue 10 Sep 2024 06:23:51:011  EDT')

INSERT INTO dbo.stringdates (MyDateValues) 
VALUES ('Tue 10 Sep 2024 06:23:28:175  EDT')

INSERT INTO dbo.stringdates (MyDateValues) 
VALUES ('Sat 7 Sep 2024 20:25:22 :233 EDT')

INSERT INTO dbo.stringdates (MyDateValues) 
VALUES ('Sat 7 Sep 2024 00:37:24 :159 EDT')

SELECT * FROM dbo.stringdates 

First, EDT is a time zone designator for Eastern Daylight Time in North America. This means your current values are local times in Daylight saving times, so you should probably also expect EST to appear when it's not Daylight saving times in North America. (see list on Wikipedia)

You should consider changing to DateTimeOffset or at least be very clear in the name of the column that its a local time.

Second, Your values are stored in a human readable string, but it's far from being "SQL Server readable". attempting to use Cast will simply fail here. What you need is to use is try_convert.
The difference between cast and convert is that you can specify the datetime's string representation format using convert, but you can't do that using cast. The difference between convert and try_convert is that convert will raise an error if it fails, while try_convert will simply return null.

So if you still want to convert to DateTime (BTW, DateTime2 is a better data type, here's why), The style value you need to use for the try_convert method is 113 (see the list here), but for that you'll have to first remove the day name and the time zone designator from the string. You do that with the help of SubString. Also, I've noticed in your sample data that you've had some values with a space before the last :, and since I don't know if this also happens in your real data, I've decided to assume it might, so I've added a replace from : to :, just to be on the safe side.

One last thing left to do to make this code safe - you need to make sure you're running this SQL Statement in the correct language settings. Running this in, say, Italian, will result with nulls all over since sep isn't a recognized month abbreviation in Italian. You do that by using set language and I'm assuming the value should be us_english (but perhaps I'm wrong here). For a list of languages your SQL Server instance supports, run select * from sys.syslanguages and see the results. Make sure your month abbreviations fits the list in the shortmonths column.

Now after all this text, it's time for the code (Which is really simple):

SET LANGUAGE us_english;
  
SELECT MyDateValues
      ,TRY_CONVERT(DateTime2(3), REPLACE(SUBSTRING(MyDateValues, 4, 25), ' :', ':'), 113) As Converted
FROM dbo.stringdates; 

and it's result: