DATEPART SQL function

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.

Datepart

Description

Example

Year

yyyy

To retrieve year from a specified date

DECLARE @OrderDate DATETIME= GETDATE();

SELECT @OrderDate AS OrderDate;

SELECT DATEPART(YEAR, @OrderDate) AS Year_OrderDate;

Quarter

To retrieve Quarter from a specified date

DECLARE @OrderDate DATETIME= GETDATE();

SELECT @OrderDate AS OrderDate;

SELECT DATEPART(Quarter, @OrderDate) AS Quarter_OrderDate;

Month

To retrieve Month from a specified date

DECLARE @OrderDate DATETIME= GETDATE();

SELECT @OrderDate AS OrderDate;

SELECT DATEPART(MONTH, @OrderDate) AS Month_OrderDate;

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.

DECLARE @OrderDate DATETIME= GETDATE();

SELECT @OrderDate AS OrderDate;

SELECT DATEPART(dy, @OrderDate) AS DayofYear_OrderDate;

Dd /d

It gives date from the specified date

DECLARE @OrderDate DATETIME= GETDATE();

SELECT @OrderDate AS OrderDate;

SELECT DATEPART(DD , @OrderDate) AS Date_OrderDate;

Week

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

DECLARE @OrderDate DATETIME= GETDATE();

SELECT @OrderDate AS OrderDate;

SELECT DATEPART(week, @OrderDate) AS Week_OrderDate;

Hour

It gives hour part from the specified date.

DECLARE @OrderDate DATETIME= GETDATE();

SELECT @OrderDate AS OrderDate;

SELECT DATEPART(HH, @OrderDate) AS Hour_OrderDate;

Minute

We can get Minute part of specified date using this.

DECLARE @OrderDate DATETIME= GETDATE();

SELECT @OrderDate AS OrderDate;

SELECT DATEPART(n, @OrderDate) AS Minute_OrderDate;

Second

We can retrieve Seconds from specified date using Seconds Datepart.

DECLARE @OrderDate DATETIME= GETDATE();

SELECT @OrderDate AS OrderDate;

SELECT DATEPART(ss, @OrderDate) AS Seconds_OrderDate;

millisecond, ms

It retrieves milliseconds value from the specified date.

DECLARE @OrderDate DATETIME= GETDATE();

SELECT @OrderDate AS OrderDate;

SELECT DATEPART(ms, @OrderDate) AS MilliSeconds_OrderDate;

Microsecond

MCS

It retrieves Microsecond

value from a specified date.

DECLARE @OrderDate DATETIME2= GETDATE();

SELECT @OrderDate AS OrderDate;

SELECT DATEPART(MCS , @OrderDate) AS MicroSeconds_OrderDate;

Nanosecond

It retrieves nanoSecond

value from a specified date.

DECLARE @OrderDate DATETIME2= GETDATE();

SELECT @OrderDate AS OrderDate;

SELECT DATEPART(NS , @OrderDate) AS NanoSeconds_OrderDate;

TZoffset

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

--This query returns time zone offset of 330 minute

SELECT DATEPART (TZoffset, '2019-04-21 00:00:01.1234567 +05:30') as OffsetMinutes;

--This query returns time zone offset of 480 minute

SELECT DATEPART (TZoffset, '2019-04-21 00:00:01.1234567 +08:00') as OffsetMinutes;

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

DECLARE @date DATETIME2= GETDATE();

SELECT @date;

SELECT DATEPART(YY, @date) AS Year,

DATEPART(QQ, @date) AS Quarter,

DATEPART(WK, @date) AS Week,

DATEPART(DY, @date) AS dayofYear,

DATEPART(MM, @date) AS Month,

DATEPART(DD, @date) AS Date,

DATEPART(hour, @date) AS Hour,

DATEPART(minute, @date) AS Minute,

DATEPART(second, @date) AS Second,

DATEPART(millisecond, @date) AS Millsecond,

DATEPART(microsecond, @date) AS Microsecond,

DATEPART(nanosecond, @date) AS Nanosecond;

ISO_WEEK

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	Country
Sunday	Saturday	1 January, First Saturday, 1-7 days of the year	United States
Monday	Sunday	1 January, First Sunday, 1-7 days of the year	Most of Europe and the United Kingdom
Monday	Sunday	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.

DECLARE @date datetimeoffset = '2008-01-06 00:00:00.000';

SELECT

DATEPART(ISO_WEEK, @date) AS 'ISO_WEEK',

DATEPART(wk, @date) AS 'US WEEK';

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

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

DECLARE @date datetimeoffset = '2012-01-01 00:00:00.000';

SELECT

DATEPART(ISO_WEEK, @date) AS 'ISO_WEEK',

DATEPART(wk, @date) AS 'US WEEK';

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

DECLARE @date datetimeoffset = '2010-01-03 00:00:00.000';

SELECT

DATEPART(ISO_WEEK, @date) AS 'ISO_WEEK',

DATEPART(wk, @date) AS '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.

DATEFIRST Value	First day of week starts from
1	Monday
2	Tuesday
3	Wednesday
4	Thursday
5	Friday
6	Saturday
7	Sunday

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.

SELECT @@DATEFIRST AS CurrentDateFIRSTValue,

CASE @@DATEFIRST

WHEN 7

THEN 'Sunday'

WHEN 6

THEN 'Saturday'

WHEN 5

THEN 'Friday'

WHEN 4

THEN 'Thursday'

WHEN 3

THEN 'Wednesday'

WHEN 2

THEN 'Tuesday'

WHEN 1

THEN 'Monday'

ELSE 'Invalid Value'

END AS FirstdayofWeek;

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.

1 2	DECLARE @OrderDate DATETIME2= GETDATE(); SELECT @OrderDate AS OrderDate,DATEPART(wk , @OrderDate) AS WeekofYear;

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.

SET DATEFIRST 1;

DECLARE @OrderDate DATETIME2= GETDATE();

SELECT @OrderDate AS OrderDate, DATEPART(wk, @OrderDate) AS WeekofYear;

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.

SELECT DATEPART(year, '00:00:00.000') AS [Year],

DATEPART(month, '00:00:00.000') AS [Month],

DATEPART(day, '00:00:00.000') AS [Day],

DATEPART(dayofyear, '00:00:00.000') AS [Dayofyear],

DATEPART(weekday, '00:00:00.000') AS [WeekDay];

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.

DECLARE @OrderDate TIME= '00:00:00.000';

SELECT DATEPART(year, @OrderDate) AS [Year],

DATEPART(month, @OrderDate) AS [Month],

DATEPART(day, @OrderDate) AS [Day],

DATEPART(dayofyear, @OrderDate) AS [Dayofyear],

DATEPART(weekday, @OrderDate) AS [WeekDay];

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

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.

CREATE TABLE Orders

(OrderID INT IDENTITY(1, 1),

OrderDate DATETIME2

);

INSERT INTO Orders(OrderDate)

VALUES(DATEADD(hh, -ROUND(60 * RAND(), 0), GETDATE()));

GO 500

Let’s view sample data in the Orders table.

SELECT *

FROM orders

ORDER BY Orderdate DESC;

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.

SELECT CAST(OrderDate AS DATE) OrderDate,

DATEPART(hour, OrderDate) [Hour],

COUNT(1) [Order Count]

FROM Orders

GROUP BY CAST(OrderDate AS DATE),

DATEPART(hour, OrderDate)

ORDER BY OrderDate,

[Order Count];

Conclusion

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.

Author
Recent Posts

Rajendra Gupta

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.

I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am 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 my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.

Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

Understanding PostgreSQL SUBSTRING function - September 21, 2024
How to install PostgreSQL on Ubuntu - July 13, 2023
How to use the CROSSTAB function in PostgreSQL - February 17, 2023

The syntax for the DATEPART SQL function

Explore the DATEPART SQL function with examples

ISO_WEEK

Specify day for the start of the week

Default values returned by DATEPART SQL

DATEPART SQL with Group By and Order By clause

Conclusion

Related posts: