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
Hour Minute Second ----------------------- 11 24 14
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')
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
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;
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.
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;
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)