TSQL month name to date

Question

Hi,

I have a table with a MNTH column that contains month prefixes - 'JUL', 'AUG', 'SEP', 'OCT', etc., up to 'JUN' of next year. My financial year for this data starts in July and ends in June. Thus, in this data set - start from 202407 up to 202506. In the next financial year - start from 202507 up to 202606, etc.

For 2024 financial year I want the below:

'JUL' should be 202407

'AUG' should be 202408

'SEP' should be 202409

'OCT' should be 202410

'NOV' should be 202411

'DEC' should be 202412

'JAN' should be 202501

'FEB' should be 202502

'MAR' should be 202503

'APR' should be 202504

'MAY' should be 202505

'JUN' should be 202506

How can I use a TSQL date function to achieve this,

Please assist,

Thanks

Answer 1

For example:

select MNTH,
   format(dateadd(month, charindex(MNTH, 'JUL AUG SEP OCT NOV DEC JAN FEB MAR APR MAY JUN') / 4 + 6 , datefromparts(year(getdate()), 1, 1)), 'yyyyMM')
from MyTable

Answer 2

while a simple case or join table would be better, you can try:

select 
    case when datepart(mm,cast('01-' + MNTH + '-2000' as date)) > 6 
        then cast(datepart(yyyy,getdate()) as varchar(4))
        else cast(datepart(yyyy,getdate())+1 as varchar(4))
    end 
    + right('0' + cast(datepart(mm,cast('01-' + MNTH + '-2000' as date)) as varchar(2)),2)
from MyTable