TSQL month name to date

Rontech10111 161 Reputation points
2024-11-11T16:52:47.7066667+00:00

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

Developer technologies | Transact-SQL
SQL Server | Other
0 comments No comments
{count} votes

Accepted answer
  1. Viorel 122.6K Reputation points
    2024-11-11T17:22:55.7733333+00:00

    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
    
    1 person found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Bruce (SqlWork.com) 77,926 Reputation points Volunteer Moderator
    2024-11-11T17:25:42.91+00:00

    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
    
    1 person found this answer helpful.
    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.