Extract partial data from a column using SQL (and maybe regex)

I’m working with SQL in MS Access. One of the columns that I import from Excel has date and time in an odd format as below:

Jan 12 2021 07:55:14 AM PST

MS Access doesn’t recognize this as date and time so doesn’t convert it as such. I’m trying to automate some steps so don’t want the user to manually delimit this column in excel and convert to DD-MM-YYYY format.

I just need to extract the dates which are almost always 11 characters in length. However, as I’m new to this, I am unable to parse it as such and the existing posts don’t help much (or maybe I can’t understand).

Answer

Assuming value always ends with ” PST” (or any 3 characters following a space), consider:

CDate(Left([fieldname], InStrRev([fieldname], " ")-1))

That returns a valid date/time. Formatting can be applied to extract only m/d/y parts. Be aware, Format function returns a string, not a true date.

Format(Left([fieldname], InStrRev([fieldname], " ")-1), "mm/dd/yyyy")