In SQL Server 2005, you may get the month and year from a datetime.

Problem

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

Solution #1

select 
datepart(month,getdate()) -- integer (1,2,3...)
,datepart(year,getdate()) -- integer
,datename(month,getdate()) -- string ('September',...)

Answered by HS.

Solution #2

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

Solution #3

You can use: starting with SQL Server 2012, you can use:

SELECT FORMAT(@date, 'yyyyMM')

Answered by CrimsonKing

Solution #4

Use:

select datepart(mm,getdate())  --to get month value
select datename(mm,getdate())  --to get name of month

Answered by don

Solution #5

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