I need the month and year from a SQL Server datetime, such as ‘Jan 2008’. I’m sorting the results by month and year. I’ve looked at functions like datepart, convert, and others, but none of them appear to be suitable for this. Is there something I’m overlooking? Is there a way to make this work?
Asked by Malik Daud Ahmad Khokhar
select datepart(month,getdate()) -- integer (1,2,3...) ,datepart(year,getdate()) -- integer ,datename(month,getdate()) -- string ('September',...)
Answered by HS.
If you mean that you want them back in that format as a string,
SELECT CONVERT(CHAR(4), date_of_birth, 100) + CONVERT(CHAR(4), date_of_birth, 120) FROM customers
Here are some more formats to consider.
Answered by robsoft
You can use: starting with SQL Server 2012, you can use:
SELECT FORMAT(@date, 'yyyyMM')
Answered by CrimsonKing
select datepart(mm,getdate()) --to get month value select datename(mm,getdate()) --to get name of month
Answered by don
Funny enough, I was just experimenting with the identical query in SQL Server and then LINQ.
SELECT DATENAME(mm, article.Created) AS Month, DATENAME(yyyy, article.Created) AS Year, COUNT(*) AS Total FROM Articles AS article GROUP BY DATENAME(mm, article.Created), DATENAME(yyyy, article.Created) ORDER BY Month, Year DESC
It generates the following results: (example).
Month | Year | Total January | 2009 | 2
Answered by Mike Geise
Post is based on https://stackoverflow.com/questions/45535/get-month-and-year-from-a-datetime-in-sql-server-2005