Get month and year from a datetime in SQL Server 2005

user_1688440 0
Viewed 810135

I need the month+year from the datetime in SQL Server like 'Jan 2008'. I'm grouping the query by month, year. I've searched and found functions like datepart, convert, etc., but none of them seem useful for this. Am I missing something here? Is there a function for this?

sql-server sql
20 Answers
select 
datepart(month,getdate()) -- integer (1,2,3...)
,datepart(year,getdate()) -- integer
,datename(month,getdate()) -- string ('September',...)
user_6180

If you mean you want them back as a string, in that format;

SELECT 
  CONVERT(CHAR(4), date_of_birth, 100) + CONVERT(CHAR(4), date_of_birth, 120) 
FROM customers

Here are the other format options

user_38970

Funny, I was just playing around writing this same query out 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 produces the following ouput (example).

Month | Year | Total

January | 2009 | 2
Mike_Geise_433800

How about this?

Select DateName( Month, getDate() ) + ' ' + DateName( Year, getDate() )
user_686540

That format doesn't exist. You need to do a combination of two things,

select convert(varchar(4),getdate(),100)  + convert(varchar(4),year(getdate()))
user_7400 
( Month(Created) + ',' + Year(Created) ) AS Date
Admin1

I had the same problem and after looking around I found this:

SELECT DATENAME(yyyy, date) AS year
FROM Income
GROUP BY DATENAME(yyyy, date)

It's working great!

Admin1

Yes, you can use datename(month,intime) to get the month in text.

user_2554950
Related