TSQL month name to date

Zimiso 141 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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,149 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,665 questions
0 comments No comments
{count} votes

Accepted answer
  1. Viorel 118.5K 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) 68,236 Reputation points
    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.