Transact-SQL includes a bunch of functions that help us work with dates and times. One of the more common tasks when working with dates is to extract the different parts of the date. For example, sometimes we only want the year, or the month. Other times we might want the day of the week. Either way, there are plenty of ways to do this in SQL Server.
In particular, the following functions allow you to return the day, month, and year from a date in SQL Server.
These functions are explained below.
The DAY(), MONTH(), and YEAR() Functions
The most obvious way to return the day, month and year from a date is to use the T-SQL functions of the same name. Yes, T-SQL has functions built specifically for the purpose of returning these three dateparts.
Here’s an example of how they work:
DECLARE @date datetime2 = '2018-06-02 08:24:14.3112042';
SELECT
DAY(@date) AS DAY,
MONTH(@date) AS MONTH,
YEAR(@date) AS YEAR;
Result:
+-------+---------+--------+ | DAY | MONTH | YEAR | |-------+---------+--------| | 2 | 6 | 2018 | +-------+---------+--------+
These functions return the datepart as an integer. They return the same result that the DATEPART() function returns for the specified datepart.
The DATEPART() Function
The DATEPART() function was built specifically for returning specified parts of a date. Therefore, we can use this function to return exactly the same result as the previous example:
DECLARE @date datetime2 = '2018-06-02 08:24:14.3112042';
SELECT
DATEPART(day, @date) AS DAY,
DATEPART(weekday, @date) AS WEEKDAY,
DATEPART(month, @date) AS MONTH,
DATEPART(year, @date) AS YEAR;
Result:
+-------+-----------+---------+--------+ | DAY | WEEKDAY | MONTH | YEAR | |-------+-----------+---------+--------| | 2 | 7 | 6 | 2018 | +-------+-----------+---------+--------+
One benefit of using this function is that you can also return other parts of the date and time. As you can see with this example, I returned the week day as well as the day (day is the day of the month, weekday is the day of the week). You can also return the various time parts, such as minutes, seconds, milliseconds, etc. For more examples, see DATEPART() Examples in SQL Server.
The DATEPART() function returns its result as an integer, and therefore, you won’t be able to get the month name or weekday name from the date. But not to worry, you can use the DATENAME() or FORMAT() functions for that.
The DATENAME() Function
The DATENAME() function is similar to the DATEPART() function, except that it returns the result as a character string instead of an integer. DATENAME() also returns month and weekday as their full name, rather than their numerical value.
Example:
DECLARE @date datetime2 = '2018-06-02 08:24:14.3112042';
SELECT
DATENAME(day, @date) AS DAY,
DATENAME(weekday, @date) AS WEEKDAY,
DATENAME(month, @date) AS MONTH,
DATENAME(year, @date) AS YEAR;
Result:
+-------+-----------+---------+--------+ | DAY | WEEKDAY | MONTH | YEAR | |-------+-----------+---------+--------| | 2 | Saturday | June | 2018 | +-------+-----------+---------+--------+
For more examples of this function, see DATENAME() Examples in SQL Server.
The FORMAT() Function
We can use the FORMAT() function to return the same values as with the DATENAME() function, and more.
This is a more versatile function than the previous ones. It allows you to format date/time as well as numeric values as strings. The return value is either nvarchar or null (depending on the input), and the length of the string is determined by the specified format.
FORMAT() also provides more options for how the datepart is presented. You can specify whether to display it as a single character, two characters, three, four, and even five in some cases.
Here are examples to demonstrate.
Day
DECLARE @date datetime2 = '2018-06-02 08:24:14.3112042';
SELECT
FORMAT(@date, 'd ') AS d,
FORMAT(@date, 'dd') AS dd,
FORMAT(@date, 'ddd') AS ddd,
FORMAT(@date, 'dddd') AS dddd;
Result:
+-----+------+-------+----------+ | d | dd | ddd | dddd | |-----+------+-------+----------| | 2 | 02 | Sat | Saturday | +-----+------+-------+----------+
Month
DECLARE @date datetime2 = '2018-06-02 08:24:14.3112042';
SELECT
FORMAT(@date, 'M ') AS M,
FORMAT(@date, 'MM') AS MM,
FORMAT(@date, 'MMM') AS MMM,
FORMAT(@date, 'MMMMM') AS MMMM;
Result:
+-----+------+-------+--------+ | M | MM | MMM | MMMM | |-----+------+-------+--------| | 6 | 06 | Jun | June | +-----+------+-------+--------+
Year
DECLARE @date datetime2 = '2018-06-02 08:24:14.3112042';
SELECT
FORMAT(@date, 'y ') AS y,
FORMAT(@date, 'yy') AS yy,
FORMAT(@date, 'yyy') AS yyy,
FORMAT(@date, 'yyyy') AS yyyy,
FORMAT(@date, 'yyyyy') AS yyyyy;
Result:
+-----+------+-------+--------+---------+ | y | yy | yyy | yyyy | yyyyy | |-----+------+-------+--------+---------| | 18 | 18 | 2018 | 2018 | 02018 | +-----+------+-------+--------+---------+
Notice that we have the option of formatting the year portion as five digits.
About the Number of Digits
When returning a datepart in digit form, the format specifier determines the minimum number of digits to return. For example, when using yyy, the year will be returned as three digits if the year is 0008 but as four digits if the year is 2008.
Example:
DECLARE @date datetime2 = '0008-06-02 08:24:14.3112042';
SELECT
FORMAT(@date, 'y ') AS y,
FORMAT(@date, 'yy') AS yy,
FORMAT(@date, 'yyy') AS yyy,
FORMAT(@date, 'yyyy') AS yyyy,
FORMAT(@date, 'yyyyy') AS yyyyy;
Result:
+-----+------+-------+--------+---------+ | y | yy | yyy | yyyy | yyyyy | |-----+------+-------+--------+---------| | 8 | 08 | 008 | 0008 | 00008 | +-----+------+-------+--------+---------+
Also, when using the single option (e.g. d) as the date specifier, you’ll need to add a space if you only want that datepart returned by itself. If you don’t do this, you’ll get more than that single datepart.
Example:
DECLARE @date datetime2 = '2008-06-02 08:24:14.3112042';
SELECT
FORMAT(@date, 'd ') AS 'Space',
FORMAT(@date, 'd') AS 'No Space',
FORMAT(@date, 'M ') AS 'Space',
FORMAT(@date, 'M') AS 'No Space',
FORMAT(@date, 'y ') AS 'Space',
FORMAT(@date, 'y') AS 'No Space';
Result:
+---------+------------+---------+------------+---------+------------+ | Space | No Space | Space | No Space | Space | No Space | |---------+------------+---------+------------+---------+------------| | 2 | 6/2/2008 | 6 | June 2 | 8 | June 2008 | +---------+------------+---------+------------+---------+------------+
For more FORMAT() examples, see How to Format the Date and Time in SQL Server.