Get month and year in sql server from date

In the previous article, I mentioned SQL server date format and converting it and Get Date From Datetime in SQL Server  but now in this article, I have mentioned how we can get only month and year from the date in SQL Server, with many possible ways.

One of the most common tasks when working with dates is to extract the different parts of the date, like month, date or just year or even weekday number, so let's take a look at some examples of extracting only month or year or month and year in SQL Server.

Using DATEPART() Function

You can declare or use existing date and pass it to Datepart function, using which you can get only month or only year or combination of both.

DECLARE @date datetime = '2022-07-23 11:24:14';

SELECT 
    DATEPART(day, @date) AS DAY, -- return day
    DATEPART(weekday, @date) AS WEEKDAY, -- returns weekday
    DATEPART(month, @date) AS MONTH, -- return Month in number
    DATEPART(year, @date) AS YEAR; -- returns year

Once I executed above query in SQL Server, I get this output

So, datepart is one of the easiest way to get month or year or day from date as you can see from above example.

If needed you can also return various Time parts like minute, hours or seconds, using Datepart function, for example, we can use below query to date time parts value.

DECLARE @date datetime = '2022-07-23 11:24:14';
SELECT 
    DATEPART(hour, @date) AS Hour,
    DATEPART(minute, @date) AS Minute,
    DATEPART(second, @date) AS Second

Output:

Hour	Minute	Second
-----------------------
11     	24     	  14

Using Format

If you are using SQL Server 2012 or above, you can use this function and get month and year from date, here is the example of it.

DECLARE @date datetime = '2022-07-23 11:24:14';
SELECT FORMAT(@date, 'MM-yyyy')

Output:

In the above SQL query, you can see MM = month and yyyy gives us year in 4 digits.

If you want Month full name, then you can use MMM as format type instead of MM in SQL Server, for example

DECLARE @date datetime = '2022-07-23 11:24:14';
SELECT FORMAT(@date, 'MMM, yyyy')

Will give the output as

Jul, 2022

Similary, you can also get date day

DECLARE @date datetime = '2022-07-23 11:24:14';
SELECT 
    FORMAT(@date, 'd ') AS d,
    FORMAT(@date, 'dd') AS dd,
    FORMAT(@date, 'ddd') AS ddd,
    FORMAT(@date, 'dddd') AS dddd;

Output:

d	dd	ddd	dddd
--------------------------------
23 	23	Sat	Saturday

Using Datename function

Datename is another function to get month or year value in SQL Server from date

This function returns a character string representing the specified datepart of the specified date.

For example

DECLARE @date datetime = '2022-07-23 11:24:14';
SELECT DATENAME(year, @date)  as year
    ,DATENAME(month, @date)  as month
    ,DATENAME(day, @date)  as day
    ,DATENAME(dayofyear,@date)  as yearDay
    ,DATENAME(weekday, @date) as weekday;  

Output:

As you can see from the above output, DATENAME() returns month and weekday as their full name, rather than their numerical value, so it cannot be useful when we need numerical form of the values, so it is better to FORMAT() function when we need month number from datetime.

You may also like to read:

Convert Month Number to Month Name in SQL

TRIM(), LTRIM(), RTRIM() Functions in SQL Server

How to check sql server version? (Various ways explained)

Import an SQL file in MySQL (Using CMD or Powershell)

Best Open Source Database Software (DBMS)

SQL Server Management Studio (SSMS) Alternatives (Mac, Windows, Linux)

How to backup SQL Server database ? (Various Ways explained)

Download and Install SQL Server Management Studio (Step by Step)