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:
- Get the year and the month from the number.
- Create a date from the year and the month.
- Get the abbreviated month name from that date.
- 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;