I am working in SQL Server, I have a column of numbers that represent the year and the month, such as:

YearandMonth
------------
202108  
202109   
202110  

How do I convert this to an output like this?

YearandMonth
------------
Aug-2021
Sept-2021
Oct-2021

Thank you in advance

CodePudding user response：

select DATE_FORMAT(CONCAT(`yearandmonth`,'01'), '%b-%Y') as 'YearandMonth' from test_table; 

by adding the 1st day of each month it provides the standard format so we can just use DATE_FORMAT() to get the result we need.

CodePudding user response：

For SQL Server 2008 this should work fine. Untested, of course:

with myTable as (
    select * from (values(202108),(202109),(202110))v(YearAndMonth)
)

select Left(DateName(month,Convert(date,Cast(YearAndMonth *100 1 as varchar(8)))),3)   '-'   Left(Cast(YearAndMonth as varchar(6)),4)
from myTable

CodePudding user response：

Steps:

1. Get the year and the month from the number.
2. Create a date from the year and the month.
3. Get the abbreviated month name from that date.
4. Concatenate month name and year.

The query:

select
  format(datefromparts(yearanddate / 100, yearanddate % 100, 1), 'MMM', 'en-US')  
  '-'  
  cast(yearanddate / 100 as varchar(4))
from mytable;