SQL Server Datepart Function

SQL Server Datepart Function

Occasionally, we may need to retrieve a part of a date, such as a day, month, or year, from a table in a SQL Server database. The datepart function provides us with that functionality, as we will see throughout this guide.

The foundation of this article is to provide you with the basics of working with the SQL Server datepart function.

Basic Usage

The datepart function allows you to fetch part of the data year, month, or day. The function returns part of the date as an integer value.

The function syntax is as:

datepart(unit, date_value);

Function Arguments

The datepart function accepts two arguments:

1. unit – This represents what part of the date SQL Server should retrieve. The accepted values in this argument are as shown in the table below:

unit                                               abbreviation
————————————————– ————
nanosecond                                         ns
microsecond                                        mcs
millisecond                                        ms
SECOND                                             s, ss
MINUTE                                             mi,n
HOUR                                               hh
week                                               wk, ww
DAY                                                dd, d
DAY OF YEAR,                                       dy, y
MONTH                                              mm, m
quarter                                            qq, q
YEAR                                               yy, yyyy

(12 ROWS affected)

2. date_value – represents the input date from which to retrieve the specified part.

Function Return Type

As mentioned, the function returns the extracted part of a date as an integer type. The function can only extract the year, month, and date from a specific input date.

SQL Server datepart() Examples

Let us look at various examples of usage of the datepart function to understand better how to use it in a T-SQL script.

Example 1:

The following example shows how to use the datepart function to retrieve a year from the specified date.

SELECT datepart(YEAR, ‘2021-12-31’) AS YEAR;

The query above should return the part of the date representing a year.

Example output is as shown:


Example 2:

In this example, we use the datepart function to retrieve parts of a date in various units.

DECLARE @input_date datetime2= CURRENT_TIMESTAMP;
SELECT datepart(YEAR, @input_date) AS YEAR,
       datepart(quarter, @input_date) AS quarter,
       datepart(MONTH, @input_date) AS MONTH,
       datepart(dayofyear, @input_date) AS dayofyear,
       datepart(DAY, @input_date) AS DAY,
       datepart(week, @input_date) AS week,
       datepart(HOUR, @input_date) AS HOUR,
       datepart(MINUTE, @input_date) AS MINUTE,
       datepart(SECOND, @input_date) AS SECOND,
       datepart(millisecond, @input_date) AS millisecond,
       datepart(microsecond, @input_date) AS microsecond,
       datepart(nanosecond, @input_date) AS nanosecond;

The above query uses the current timestamp to extract various units using the datepart function.

The resulting values are as shown:

Example 3

We can also use the datepart in a real database. For example, the query below extracts year from a column:

USE WideWorldImporters;
SELECT top 10 datepart(DAY, ExpectedDeliveryDate) AS DAY FROM Sales.Orders
    WHERE ExpectedDeliveryDate IS NOT NULL;

In this example, we use the datepart function to get the day of the expected delivery date.

Final Thoughts.

This guide helps you to explore the functionality of the SQL Server datepart function. Using the datepart function, you can extract various date units and use them in other columns.

We hope you enjoyed the tutorial. Stay tuned for more.

Leave a Reply

Your email address will not be published. Required fields are marked *