3 Letter Month Abbreviation to Date-CodePudding

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).

CodePudding user response：

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
;

CodePudding user response：

For SQL Server:

convert(date, '01 '   LEFT('SEP22', 3)   ' '   RIGHT('SEP22', 2), 6)

When SQL Server converts the date from a string with a 2-digit year, the cutoff is at 50:

declare @myMonth table (
  mo varchar(5)
)
declare @i int = 0

while @i < 100
begin
  set @i = @i   1
  insert @myMonth
  values ('SEP'   RIGHT('0'   CAST(@i AS VARCHAR(5)), 2))
end


SELECT mo
, convert(date, '01 '   LEFT(mo, 3)   ' '   RIGHT(mo, 2), 6)
FROM @myMonth

For Oracle:

TO_DATE('01' || 'SEP22', 'DDMONYY')

For Oracle, all of the dates are after 2000:

CREATE TABLE MYMONTH (
  MO VARCHAR2(6) NOT NULL
)
;

INSERT INTO MYMONTH (MO)
SELECT 'SEP' || SUBSTR('0' || CAST(N AS VARCHAR2(5)), -2)
FROM (
  SELECT (0   ROWNUM) N
  FROM DUAL CONNECT BY ROWNUM <= 100
)
;

SELECT MO
, TO_CHAR(TO_DATE('01' || MO, 'DDMONYY'), 'MM/DD/YYYY')
FROM MYMONTH 
;