Get month and year from a datetime in SQL Server 2005

Question

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?

@Evan Carroll - does this constitute hijacking the question? If you have a superior answer to the question, should it not be supplied as an answer to the question instead of pointing people to a different question? — STLDev, Apr 04 '17 at 00:25
@STLDeveloper how is it hijacking. The question calls for 2005? I didn't put that there. I'm not looking for 2005.. Unfortunately, the best answer here doesn't work on 2005, it's 2012-explicit. Should I downvote the 2005 answers for being archaic? — Evan Carroll, Apr 04 '17 at 00:28
@STLDeveloper actually, I don't care here. =) my official stance has always been to advise people to use PostgreSQL, not sure why I'm trying to help. — Evan Carroll, Apr 04 '17 at 00:31
It just seemed odd to me that you went back to create a new version of the question for a different version of the product, and then to answer that question, which I guess is fine. — STLDev, Apr 04 '17 at 00:33

score 176 · Answer 1 · edited Mar 14 '13 at 11:03

176

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

edited Mar 14 '13 at 11:03

SWeko

30,434
10
71
106

answered Sep 05 '08 at 12:00

HS.

15,442
8
42
48

So, I think this is what I want, but I do not see how TSQL would understand the difference between `datepart(month,getdate())` and `datepart(year,getdate())`. What are **month** and **year**? – Jun 03 '13 at 14:30
4

@jp2code **month** and **year** are basically constants defined here: http://msdn.microsoft.com/en-us/library/ms174420.aspx. TSQL understands them the same way you do, by reading them :) – Zachary Yates Jun 05 '13 at 23:44
1

LOL - Thanks Zach. I starred at that a long time before figuring it out on a different site. – Jun 06 '13 at 12:13
Much cleaner than convert function. #cleanCode – RBT Jan 09 '17 at 02:50

score 80 · Accepted Answer · edited May 16 '11 at 15:42

80

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

edited May 16 '11 at 15:42

Sampson

265,109
74
539
565

answered Sep 05 '08 at 11:12

robsoft

5,525
4
35
47

I tried using CONVERT(CHAR(4), GetDate(), 100) on my SQL Server and I got "09 1" instead. I got the space and one "1" for the 17. using "select CONVERT(varchar, GetDate(), 100)" yield "09 17 2009 4:59PM" – Nap Sep 17 '09 at 08:00
1

That's weird - from the docs I linked to, 100 should return a three-letter month abbreviation and a space, if chucked into a CHAR(4). But then, the result you've posted for a CONVERT(varchar, getdate(), 100) looks different to what I'd expect to see. Which version of SQLServer are you using? Which localisation/internationalisation settings are you using (not that this should matter to a 100 format). – robsoft Sep 17 '09 at 08:34

score 71 · Answer 3 · answered Nov 24 '14 at 13:16

71

Beginning with SQL Server 2012, you can use:

SELECT FORMAT(@date, 'yyyyMM')

answered Nov 24 '14 at 13:16

CrimsonKing

2,696
1
14
11

1

Well played sir... this answered my question in 2012 – J.S. Orris Mar 17 '15 at 00:35
I'm glad I scrolled this far, this is neat and simple. Thanks! – Niklas Jun 15 '16 at 14:32
This is a valuable contribution but it should have been answered elsewhere, http://stackoverflow.com/a/43196370/124486 – Evan Carroll Apr 04 '17 at 00:23

score 13 · Answer 4 · answered Jun 18 '16 at 11:15

13

In SQL server 2012, below can be used

select FORMAT(getdate(), 'MMM yyyy')

This gives exact "Jun 2016"

answered Jun 18 '16 at 11:15

webbuilder

173
1
9

score 13 · Answer 5 · edited Mar 23 '11 at 04:45

13

Use:

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

edited Mar 23 '11 at 04:45

OMG Ponies

325,700
82
523
502

answered Nov 22 '10 at 09:45

don

131
1
2

score 12 · Answer 6 · edited May 21 '11 at 16:51

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

score 7 · Answer 7 · edited May 16 '11 at 15:40

7

How about this?

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

edited May 16 '11 at 15:40

Sampson

265,109
74
539
565

answered May 14 '09 at 04:40

GordyII

7,067
16
51
69

score 6 · Answer 8 · edited May 21 '11 at 16:50

6

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()))

edited May 21 '11 at 16:50

Peter Mortensen

30,738
21
105
131

answered Sep 05 '08 at 11:08

SQLMenace

132,095
25
206
225

score 5 · Answer 9 · answered Jun 01 '14 at 15:01

5

the best way to do that is with :

dateadd(month,datediff(month,0,*your_date*),0)

it will keep your datetime type

answered Jun 01 '14 at 15:01

cyber cyber1621

167
2
6

1

This is by far the best answer for forcing any date to be just the month and year, ignoring day and time components. Much better than the accepted answer because the information can be sorted correctly and functions correctly as a date in downstream reporting tools. – Jamie Thomas Dec 01 '16 at 16:12
I wholeheartedly agree that this is by far the best way to get the beginning of the month. It keeps the data type datetime, and leaves the rendering up to the front end, where it should be. This is the correct way to do things like group rows by the month of a field. – Jeff Breadner Jun 14 '18 at 22:52

score 5 · Answer 10 · edited May 16 '11 at 15:40

5

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

edited May 16 '11 at 15:40

Sampson

265,109
74
539
565

answered Jan 15 '09 at 11:07

score 2 · Answer 11 · answered Oct 17 '13 at 22:16

2

cast(cast(sq.QuotaDate as date) as varchar(7))

gives "2006-04" format

answered Oct 17 '13 at 22:16

Gareth Thomas

420
3
4

score 2 · Answer 12 · answered Apr 08 '15 at 05:51

2

The question is about SQL Server 2005, many of the answers here are for later version SQL Server.

select convert (varchar(7), getdate(),20)
--Typical output 2015-04

SQL Server 2005 does not have date function which was introduced in SQL Server 2008

answered Apr 08 '15 at 05:51

TheTechGuy

16,560
16
115
136

score 2 · Answer 13 · edited Mar 08 '17 at 20:21

2

returns the full month name, -, full year e.g. March-2017

CONCAT(DATENAME(mm, GetDate()), '-', DATEPART(yy, GetDate()))

edited Mar 08 '17 at 20:21

CDspace

2,639
18
30
36

answered Mar 08 '17 at 20:15

RobertC

21
1

2

There are already 18 other answers to this question. What new information does your answer add? – stannius Mar 08 '17 at 20:18

score 1 · Answer 14 · answered Apr 11 '13 at 19:18

Converting the date to the first of the month allows you to Group By and Order By a single attribute, and it's faster in my experience.

declare @mytable table(mydate datetime)
declare @date datetime
set @date = '19000101'
while @date < getdate() begin
    insert into @mytable values(@date)
    set @date = dateadd(day,1,@date)
end

select count(*) total_records from @mytable

select dateadd(month,datediff(month,0,mydate),0) first_of_the_month, count(*) cnt
from @mytable
group by dateadd(month,datediff(month,0,mydate),0)

score 1 · Answer 15 · edited Oct 08 '13 at 09:57

---Lalmuni Demos---
create table Users
(
userid int,date_of_birth date
)
---insert values---
insert into Users values(4,'9/10/1991')

select DATEDIFF(year,date_of_birth, getdate()) - (CASE WHEN (DATEADD(year, DATEDIFF(year,date_of_birth, getdate()),date_of_birth)) > getdate() THEN 1 ELSE 0 END) as Years, 
MONTH(getdate() - (DATEADD(year, DATEDIFF(year, date_of_birth, getdate()), date_of_birth))) - 1 as Months, 
DAY(getdate() - (DATEADD(year, DATEDIFF(year,date_of_birth, getdate()), date_of_birth))) - 1 as Days,
from users

score 1 · Answer 16 · edited May 21 '11 at 16:52

1

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!

edited May 21 '11 at 16:52

Peter Mortensen

30,738
21
105
131

answered May 09 '09 at 04:50

score 0 · Answer 17 · edited Mar 22 '11 at 12:43

0

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

edited Mar 22 '11 at 12:43

Rob Hruska

118,520
32
167
192

answered Jan 21 '10 at 08:24

Alok Kumar

21
1

score 0 · Answer 18 · answered Nov 11 '15 at 02:52

0

  ,datename(month,(od.SHIP_DATE)) as MONTH_

Answer: MONTH_ January January September October December October September

answered Nov 11 '15 at 02:52

khmer angkor

1

score 0 · Answer 19 · edited Jun 03 '17 at 10:34

0

It's work great.

DECLARE @pYear VARCHAR(4)

DECLARE @pMonth VARCHAR(2)

DECLARE @pDay VARCHAR(2)

SET @pYear  = RIGHT(CONVERT(CHAR(10), GETDATE(), 101), 4)

SET @pMonth = LEFT(CONVERT(CHAR(10), GETDATE(), 101), 2)

SET @pDay   = SUBSTRING(CONVERT(CHAR(10), GETDATE(), 101), 4,2)

SELECT @pYear,@pMonth,@pDay

edited Jun 03 '17 at 10:34

tso

4,732
2
22
32

answered Jun 03 '17 at 06:09

GanbatSu

1
1

score -3 · Answer 20 · edited May 21 '11 at 16:52

-3

The following works perfectly! I just used it, try it out.

date_format(date,'%Y-%c')

edited May 21 '11 at 16:52

Peter Mortensen

30,738
21
105
131

answered Mar 22 '11 at 10:29

Matteo

1

2

this is not a valid sql server function or statement – franko_camron Aug 08 '12 at 20:17

Get month and year from a datetime in SQL Server 2005

20 Answers20

Linked