3 Letter Month Abbreviation to Date

September 16, 2022

I have a list of months formatted with the 3 letter abbreviation followed by the last 2 numbers of the year.
For example, this current month would be SEP22. I need to find a way to turn this from a varchar into a date/datetime value, pointing to the first day of the month.

So, if I have ‘SEP22’, I need to get the output of September 1st, 2022 as a date/datetime object.

I can find a lot of data on turning the datetime value or numeric month into the abbreviation, but not the other way around.

I know I can make a giant case statement, but I was wondering if there’s an easier method to read this type of data.

Any and all help is appreciated.

EDIT: I know the data is in the current century, I know where the data comes from and when it started being collected, all of this data has been collected in or after August 2013 (AUG13).

>Solution :

Try this:

SELECT
    CAST(
        LEFT(StringDate, 3) -- e.g. SEP
        +' 1 20' -- assumes 21st century
        + RIGHT(StringDate, 2) -- e.g. 22
        AS DATE
    ) AS ActualDate
;