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?

  • What is the T-SQL syntax to connect to another SQL Server?
  • PHP Byte Array to Base64 (Byte Array From Mssql)
  • Is this SQL Server 2012 “exceeds single hemisphere” error actually an SRID error?
  • Getting a strange error when Try to save a SSRS report
  • SQL table partition on same criteria as clustered index
  • How do I receiving job notifications by email?
  • 20 Solutions collect form web for “Get month and year from a datetime in SQL Server 2005”

    If you mean you want them back as a string, in that format;

      CONVERT(CHAR(4), date_of_birth, 100) + CONVERT(CHAR(4), date_of_birth, 120) 
    FROM customers

    Here are the other format options

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

    Beginning with SQL Server 2012, you can use:

    SELECT FORMAT(@date, 'yyyyMM')


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

    Funny, I was just playing around writing this same query out in SQL Server and then LINQ.

        DATENAME(mm, article.Created) AS Month, 
        DATENAME(yyyy, article.Created) AS Year, 
        COUNT(*) AS Total 
    FROM Articles AS article 
        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
    ( Month(Created) + ',' + Year(Created) ) AS Date

    How about this?

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

    In SQL server 2012, below can be used

    select FORMAT(getdate(), ‘MMM yyyy’)

    This gives exact “Jun 2016”

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

    the best way to do that is with :


    it will keep your datetime type

    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!

    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)
    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)
    ---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
    cast(cast(sq.QuotaDate as date) as varchar(7))

    gives “2006-04” format

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

    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

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


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

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

    It’s work great.

    DECLARE @pMonth 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

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

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.