Rajendra Gupta
ISO_WEEK

DATEPART SQL function

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.

Datepart

Description

Example

Year

yyyy

yy

To retrieve year from a specified date

SQL DATEPART with examples

Quarter

qq

q

To retrieve Quarter from a specified date

Get Quarter values with SQL DATEPART

Month

Mm

m

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

Wk

Ww

Week

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

Hour

Hh

It gives hour part from the specified date.

Get hour values with SQL DATEPART

Minute

N

We can get Minute part of specified date using this.

Get Minutes values with SQL DATEPART

Second

Ss

s

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

Microsecond

MCS

It retrieves Microsecond

value from a specified date.

Get Microseconds values with SQL DATEPART

Nanosecond

NS

It retrieves nanoSecond

value from a specified date.

Get NanoSeconds values with SQL DATEPART

TZoffset

tz

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

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.

ISO_WEEK

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.

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.

ISO_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.

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.

SQL DATEFIRST

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.

DATEFIRST

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.

DATEFIRST example

Let’s verify current DATEFIRST setting in the instance

DATEFIRST example

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

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.

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta
615 Views