Get month and year from a datetime in sql server 2005

Question

Ok, this should be an easy question to answer. 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?

Answer 1

that format doesn't exists you need to do a combo of two things

select convert(varchar(4),getdate(),100)  + convert(varchar(4),year(getdate()))

Answer 2

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

Answer 3

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

Answer 4

(Month(Created) + ',' + Year(Created)) AS Date

Answer 5

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

Produces the following ouput (example).

Month | Year | Total

January | 2009 | 2

bah I just realized someone bumped an old question...

Answer 6

Hey... If it's still relevant 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!

Answer 7

How about this?

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

Answer 8

yes we can use datename(month,intime) to get the month in text

Alok