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?
This question is tagged with
~ Asked on 2008-09-05 11:02:44
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
~ Answered on 2008-09-05 11:12:32
select datepart(month,getdate()) -- integer (1,2,3...) ,datepart(year,getdate()) -- integer ,datename(month,getdate()) -- string ('September',...)
~ Answered on 2008-09-05 12:00:45
Beginning with SQL Server 2012, you can use:
SELECT FORMAT(@date, 'yyyyMM')
~ Answered on 2014-11-24 13:16:12
select datepart(mm,getdate()) --to get month value select datename(mm,getdate()) --to get name of month
~ Answered on 2010-11-22 09:45:47
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
~ Answered on 2009-01-15 11:19:51
In SQL server 2012, below can be used
select FORMAT(getdate(), 'MMM yyyy')
This gives exact "Jun 2016"
~ Answered on 2016-06-18 11:15:09
How about this?
Select DateName( Month, getDate() ) + ' ' + DateName( Year, getDate() )
~ Answered on 2009-05-14 04:40:54
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()))
~ Answered on 2008-09-05 11:08:54
( Month(Created) + ',' + Year(Created) ) AS Date
~ Answered on 2009-01-15 11:07:48
the best way to do that is with :
it will keep your datetime type
~ Answered on 2014-06-01 15:01:01
returns the full month name, -, full year e.g.
CONCAT(DATENAME(mm, GetDate()), '-', DATEPART(yy, GetDate()))
~ Answered on 2017-03-08 20:15:31
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 on 2015-04-08 05:51:16
---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
~ Answered on 2013-10-08 09:39:40
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)
~ Answered on 2013-04-11 19:18:25
cast(cast(sq.QuotaDate as date) as varchar(7))
gives "2006-04" format
~ Answered on 2013-10-17 22:16:55
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!
~ Answered on 2009-05-09 04:50:22
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
~ Answered on 2017-06-03 06:09:46
,datename(month,(od.SHIP_DATE)) as MONTH_
Answer: MONTH_ January January September October December October September
~ Answered on 2015-11-11 02:52:32
Yes, you can use
datename(month,intime) to get the month in text.
~ Answered on 2010-01-21 08:24:28
The following works perfectly! I just used it, try it out.
~ Answered on 2011-03-22 10:29:23