3 Letter Month Abbreviation to Date

Question

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

How do you know it's 2022 and not 1922? Also what RDBMS the syntax varies For example: https://stackoverflow.com/questions/3139051/sql-converting-string-mmm-yy-to-date or oracle `SELECT to_date('AUG09','MONYY') FROM dual;` not all SQL is the same sadly. — xQbert, Sep 15 '22 at 21:10

score 1 · Accepted Answer · answered Sep 15 '22 at 21:16

1

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
;

answered Sep 15 '22 at 21:16

John K.

480
4
8

This is T-SQL but should get you on the right track if you're using another DBMS. – John K. Sep 15 '22 at 21:17
1

This worked perfectly, thank you! I've marked it as a solution. – Jwest Sep 15 '22 at 21:23
It could be as simple as Select try_convert(date,stuff('Sep22',4,0,'20')) ... SQL Server will assume the 1st of the month. – John Cappelletti Sep 28 '22 at 18:21

score 0 · Answer 2 · answered Sep 15 '22 at 21:55

0

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 
;

answered Sep 15 '22 at 21:55

dougp

2,810
1
8
31

Your SQL Server approach could be as simple as try_convert(date,stuff('Sep22',4,0,'20')) – John Cappelletti Sep 28 '22 at 18:18
For 2000-2050, that's true. What if the input is SEP75? The OP was unclear regarding which century to use for which 2-digit year values. I didn't want to assume the first 2 digits of the year are always 20. My response stuck to how the RDBMS would answer that part of the question. If the OP provides more clarity, I may revise my answer. – dougp Sep 28 '22 at 18:32
I think the real question is why the data modeler or application developer decided to use MMMYY format to store dates. I assume that's just nonsense the OP is being forced to deal with. – dougp Sep 28 '22 at 18:34
Over the years I've had to consume some horrible data from various sources... You just roll with it and create a solution. – John Cappelletti Sep 28 '22 at 18:37

3 Letter Month Abbreviation to Date

2 Answers2