Get month and year from a datetime in SQL Server 2005
SqlSql ServerSql Problem Overview
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 Solutions
Solution 1 - Sql
select
datepart(month,getdate()) -- integer (1,2,3...)
,datepart(year,getdate()) -- integer
,datename(month,getdate()) -- string ('September',...)
Solution 2 - Sql
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
Solution 3 - Sql
Beginning with SQL Server 2012, you can use:
SELECT FORMAT(@date, 'yyyyMM')
Solution 4 - Sql
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
Solution 5 - Sql
Use:
select datepart(mm,getdate()) --to get month value
select datename(mm,getdate()) --to get name of month
Solution 6 - Sql
In SQL server 2012, below can be used
select FORMAT(getdate(), 'MMM yyyy')
This gives exact "Jun 2016"
Solution 7 - Sql
How about this?
Select DateName( Month, getDate() ) + ' ' + DateName( Year, getDate() )
Solution 8 - Sql
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()))
Solution 9 - Sql
( Month(Created) + ',' + Year(Created) ) AS Date
Solution 10 - Sql
the best way to do that is with :
dateadd(month,datediff(month,0,*your_date*),0)
it will keep your datetime type
Solution 11 - Sql
returns the full month name, -, full year e.g. March-2017
CONCAT(DATENAME(mm, GetDate()), '-', DATEPART(yy, GetDate()))
Solution 12 - Sql
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!
Solution 13 - Sql
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)
Solution 14 - Sql
---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
Solution 15 - Sql
cast(cast(sq.QuotaDate as date) as varchar(7))
gives "2006-04" format
Solution 16 - Sql
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
Solution 17 - Sql
Yes, you can use datename(month,intime) to get the month in text.
Solution 18 - Sql
,datename(month,(od.SHIP_DATE)) as MONTH_
Answer: MONTH_ January January September October December October September
Solution 19 - Sql
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
Solution 20 - Sql
The following works perfectly! I just used it, try it out.
date_format(date,'%Y-%c')