Reputation: 13730

Get month and year from a datetime in SQL Server 2005

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?

Upvotes: 97

Answers (20)

GanbatSu

Reputation: 1

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

Upvotes: 0

RobertC

Reputation: 21

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

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

Upvotes: 2

webbuilder

Reputation: 173

In SQL server 2012, below can be used

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

This gives exact "Jun 2016"

Upvotes: 13

khmer angkor

Reputation: 1

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

Answer: MONTH_ January January September October December October September

Upvotes: 0

TheTechGuy

Reputation: 17394

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

Upvotes: 2

CrimsonKing

Reputation: 2896

Beginning with SQL Server 2012, you can use:

SELECT FORMAT(@date, 'yyyyMM')

Upvotes: 72

cyber cyber1621

Reputation: 167

the best way to do that is with :

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

it will keep your datetime type

Upvotes: 5

Gareth Thomas

Reputation: 430

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

gives "2006-04" format

Upvotes: 2

Lalmuni Singh

Reputation: 7

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

Upvotes: 1

Ron Smith

Reputation: 3266

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)

Upvotes: 1

HS.

Reputation: 15720

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

Upvotes: 177

Matteo

Reputation: 1

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

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

Upvotes: -3

Lior

Reputation:

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!

Upvotes: 1

Mike Geise

Reputation: 825

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

Upvotes: 12

SQLMenace

Reputation: 135181

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

Upvotes: 6

robsoft

Reputation: 5585

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

Upvotes: 82

GordyII

Reputation: 7257

How about this?

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

Upvotes: 7

Tom

Reputation:

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

Upvotes: 5

don

Reputation: 141

Use:

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

Upvotes: 14

Alok Kumar

Reputation: 21

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

Upvotes: 0

Get month and year from a datetime in SQL Server 2005

Answers (20)

Related Questions