Rajendra Gupta


April 23, 2019 by

This article explores the DATEPART SQL function and its use in writing t-SQL queries. In the previous article, SQL Convert Date Functions and Formats, we explored various data formats and convert them using SQL Convert function.

Usually, WHEN we use dates in SQL Server tables. Sometimes, we require retrieving A specific part of the date such as day, month or year from the existing SQL tables. We can use THE DATEPART SQL function to do this.

The syntax for the DATEPART SQL function

DATEPART (interval, date)

We need to pass two parameters in this function.

  • Interval: We specify the interval that we want to get from a specified date. The DATEPART SQL function returns an integer value of specific interval. We will see values for this in the upcoming section.
  • Date: We specify the date to retrieve the specified interval value. We can specify direct values or use expressions to return values from the following data types.
    • Date
    • DateTime
    • Datetimeoffset
    • Datetime2
    • Smalldatetime
    • Time

Explore the DATEPART SQL function with examples

In this section, let’s explore DATEPART SQL with examples and different interval values.







To retrieve year from a specified date

SQL DATEPART with examples




To retrieve Quarter from a specified date

Get Quarter values with SQL DATEPART




To retrieve Month from a specified date

Get Month values with SQL DATEPART

Dy / y

We get Day of year in a specified date. For example, Jan 1 2019 is the 1st day of the year, and Dec 31, 2019, is the 365th day of the year.

Get Day of year values with SQL DATEPART

Dd /d

It gives date from the specified date

Get Date values with SQL DATEPART




We get week number of current date in specified date. We have 52 weeks in 2019.

Get current week of year values with SQL DATEPART



It gives hour part from the specified date.

Get hour values with SQL DATEPART



We can get Minute part of specified date using this.

Get Minutes values with SQL DATEPART




We can retrieve Seconds from specified date using Seconds Datepart.

Get Seconds values with SQL DATEPART

millisecond, ms

It retrieves milliseconds value from the specified date.

Get Milliseconds values with SQL DATEPART



It retrieves Microsecond

value from a specified date.

Get Microseconds values with SQL DATEPART



It retrieves nanoSecond

value from a specified date.

Get NanoSeconds values with SQL DATEPART



TZOFFSET computes the time zone offset between the local time zone and GMT.
We can have multiple timezones across countries such as following.
GMT-5 +18000 – United States
 GMT+10 -36000 – Australia

Get offset values with SQL DATEPART

Get offset values with SQL DATEPART

Let’s use various DATEPART SQL function parameters in a single SQL statement. It helps us to understand the breakdown of a specified date.

SQL Server datepart function - Examples with various values


While working with dates, we might get different values in different countries of the world for following things.

  • Week number
  • Day of the Week

It depends on the Country and language. In ISO 8601 weekday system, we consider a week in which Thursday comes. It is the most common week numbering system.

Accordingly, in the year 2004, the first week occurs from Monday, 29 December 2003 to Sunday, 4 January 2004.


First day of week

Last day of week

The first week of the year contains




1 January,

First Saturday,

1-7 days of the year

United States



1 January,

First Sunday,

1-7 days of the year

Most of Europe and the United Kingdom



4 January,

First Thursday,

4-7 days of the year

ISO 8601, Norway, and Sweden

Let’s run the following query with DATEPART SQL to return ISO 8601 week and US week no.

In the following screenshot, we can see for 6th January 2008, ISO week is first and US week is 2nd.

SQL Server datepart function - ISO_WEEK

Similarly, January 1st, 2012 is having the first week in ISO 8601 and week 53rd in US week.

Let’s look at one more example. For 3rd January 2010, we have 53rd ISO week and 2nd US week.


Specify day for the start of the week

We can set the first day of the week with SQL DATEFIRST function. We can specify value 1 to 7. If we specify the value 1, it considers Monday as the first day of the week. We can refer to the following table for specifying a value in SQL DATEFIRST.


First day of week starts from















We can get current DATEFIRST value using system variable @@DATEFIRST. Execute the following query to get the default value of SQL DATEFIRST and the first day of the week.

In the following screenshot, we can see in my system, it having SQL DATEFIRST value 7. It starts on the first day of the week from Sunday.


Execute the following query to get the current week of the year. We can see 21st April 2019 is week 17 of the current year.


Suppose we want to change the first day of the week to Monday. We can do it using SET DATEFIRST command and specify a value from the table specified above.

In the output, we can see the current week of the year is 16. It is because SQL considered Monday as the first day of the week.


Let’s verify current DATEFIRST setting in the instance


Default values returned by DATEPART SQL

Suppose we do not have a date format such as yyyy-mm-dd hh:ss: Mmm. If we do not have a date datatype, we get default values as the output of DATEPART SQL.

In the following example, we want to retrieve values such as Year, Month, and Day. We are providing input in the form of hh:mm: ss.mmm.

Default values returned by SQL DATEPART

In earlier examples, we used variables in DATEPART SQL to get respective values. In the following query, we specified a variable with the datatype. Execute this query.

We get the following error message that datatype is not supported by date function DATEPART SQL.

Error for Default values returned by SQL DATEPART

DATEPART SQL with Group By and Order By clause

We can use the DATEPART SQL function with Group By clause as well to group data based on a specified condition.

Let’s create a sample table and insert hourly data in it.

Let’s view sample data in the Orders table.

Sample data

Suppose we want to get hourly data from the Orders table. We can use the DATEPART SQL function in a Group By clause to get hourly data. Execute the following query, and in the output, you can see the count of hourly orders data. We further used Order By clause to sort results.

SQL DATEPART with Group By and Order By clause


In this article, we explore the use of SQL DATAPART function with examples. You should be familiar with this useful SQL function to improve your T-SQL coding skill. I hope you found this article helpful.

Rajendra Gupta
Functions, SQL commands

About Rajendra Gupta

As an MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of large companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date. He is the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups. Based on his contribution to the SQL Server community, he has been recognized with various awards including the prestigious “Best author of the year" continuously in 2020 and 2021 at SQLShack. Raj is always interested in new challenges so if you need consulting help on any subject covered in his writings, he can be reached at rajendra.gupta16@gmail.com View all posts by Rajendra Gupta