SQL SERVER

SQL DATEPART: Get a Part of a Date in SQL Server

intro

Let's learn everything you need to know about the SQL DATEPART function to extract specific components from a date in T-SQL.

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT

Dates are complex data types that consist of several parts, including, year, month, day, etc. The selection of these parts is critical for filtering or aggregating data, for example, to obtain records referring only to a specific month or year. This is where the SQL DATEPART function comes in!

In this article, you will learn more about the DATEPART SQL Server function, from its syntax to its use. Jump in!

What Is the SQL DATEPART Function?

DATEPART is an SQL Server function that returns an integer representing the specified part of a given date. In detail, it extracts a specific part of a date, datetime, or time value, such as the year, month, day, hour, minute, etc.

The SQL DATEPART function is particularly useful to isolate or manipulate certain components of a date for analysis or formatting purposes. While it is a T-SQL-specific function, other database management systems provide similar functions:

SQL Server DATEPART: Syntax and First Example

The syntax of the DATEPART SQL Server function is:

Copy
        
1 DATEPART(datepart, date)

Where:

  • datepart is the specific part of the date argument to return as an integer.
  • date is an expression that resolves to one of the following data types: date, datetime, datetimeoffset, datetime2, smalldatetime, time. This can be a column expression, general SQL date expression, string literal, or user-defined variable.

The datepart argument accepts only the following fixed values:

datepartExplanation
year, yy, or yyyyExtracts the year from the date
quarter, qq, or qExtracts the quarter of the year from the date (1 to 4)
month, mm, or mExtracts the month from the date (1 to 12)
dayofyear, dy, or yExtracts the day of the year from the date (1 to 366)
day, dd, or dExtracts the day of the month from the date (1 to 31)
week, wk, or wwExtracts the week number of the year from the date
weekday, or dwExtracts the day of the week from the date (1 to 7)
hour, or hhExtracts the hour from the time (0 to 23)
minute, mi, or nExtracts the minute from the time (0 to 59)
second, ss, or sExtracts the second from the time (0 to 59)
millisecond, or msExtracts the millisecond from the time (0 to 999)
microsecond, or mcsExtracts the microsecond from the time
nanosecond, or nsExtracts the nanosecond from the time
tzoffset, or tzExtracts the time zone offset from UTC
iso_week, isowk, or isowwExtracts the https://www.iso.org/iso-8601-date-and-time-format.html week number of the year from the date

As you can see, each datepart has one or more equivalent abbreviated versions.

Note: The tzoffset datepart is supported only on data types that can implicitly be converted to datetimeoffset or datetime2. Otherwise, SQL Server will raise the following error:

Copy
        
1 [Code: 9810, SQL State: S1000] The datepart tzoffset is not supported by date function datepart for data type datetime.

To better understand how this function works, consider the parametric query below:

Copy
        
1 SELECT DATEPART(datepart, '2024-10-30 14:11:37.1491582 +06:12')

Based on the value of datepart, you will get the following results:

  • year, yy, yyyy: 2024
  • quarter, qq, q: 4
  • month, mm, m: 10
  • dayofyear, dy, y: 304
  • day, dd, d: 30
  • week, wk, ww: 44
  • weekday, dw: 4 (assuming SQL Server, where 1 = Sunday, 2 = Monday, etc.)
  • hour, hh: 14
  • minute, mi, n: 11
  • second, ss, s: 37
  • millisecond, ms: 149
  • microsecond, mcs: 149158
  • nanosecond, ns: 149158200
  • tzoffset, tz: 372
  • iso_week, isowk, isoww: 44

You can test that with the following SQL Server DATEPART query:

Copy
        
1 SELECT 2 DATEPART(year, '2024-10-30 14:11:37.1491582 +06:12') AS Year, 3 DATEPART(quarter, '2024-10-30 14:11:37.1491582 +06:12') AS Quarter, 4 DATEPART(month, '2024-10-30 14:11:37.1491582 +06:12') AS Month, 5 DATEPART(dayofyear, '2024-10-30 14:11:37.1491582 +06:12') AS DayOfYear, 6 DATEPART(day, '2024-10-30 14:11:37.1491582 +06:12') AS Day, 7 DATEPART(week, '2024-10-30 14:11:37.1491582 +06:12') AS Week, 8 DATEPART(weekday, '2024-10-30 14:11:37.1491582 +06:12') AS WeekDay, 9 DATEPART(hour, '2024-10-30 14:11:37.1491582 +06:12') AS Hour, 10 DATEPART(minute, '2024-10-30 14:11:37.1491582 +06:12') AS Minute, 11 DATEPART(second, '2024-10-30 14:11:37.1491582 +06:12') AS Second, 12 DATEPART(millisecond, '2024-10-30 14:11:37.1491582 +06:12') AS Millisecond, 13 DATEPART(microsecond, '2024-10-30 14:11:37.1491582 +06:12') AS Microsecond, 14 DATEPART(nanosecond, '2024-10-30 14:11:37.1491582 +06:12') AS Nanosecond, 15 DATEPART(tzoffset, '2024-10-30 14:11:37.1491582 +06:12') AS TZOffset, 16 DATEPART(iso_week, '2024-10-30 14:11:37.1491582 +06:12') AS ISO_Week;

Note: DATEPART(year, date), DATEPART(month, date), and DATEPART(day, date) return the same values as the functions YEAR, MONTH, and DAY, respectively.

If the part of the date you want to get is not on the specified date, SQL Server will extract it from the following default date:

Copy
        
1 1900-01-01 00:00:00.000000000 +00:00

In other words, consider the query below:

Copy
        
1 SELECT DATEPART(year, '16:18')

That will return:

Copy
        
1 1900

The SQL DATEPART function can be used in the SELECT list of a T-SQL query as well as in the WHERE, HAVING, GROUP BY, and ORDER BY clauses.

Use Cases of the T-SQL DATEPART Function

Now that you know what the T-SQL DATEPART function is and how to use it, you are ready to explore some of its most common use cases.

The queries in this section will be executed on the following Orders table:

The data in the Orders table in DbVisualizer
The data in the Orders table in DbVisualizer

The sample queries below will be executed in DbVisualizer, the database client with the highest user satisfaction in the market.

Filtering Data by Specific Time Periods

The SQL DATEPART function gives you the ability to retrieve records that fall within a specific month, year, day, etc. This is useful for filtering data by specific time periods.

For example, assume you want to retrieve all orders placed in 2022. You could easily achieve that with a DATEPART SQL Server query as follows:

Copy
        
1 SELECT * 2 FROM Orders 3 WHERE DATEPART(year, OrderDate) = 2022;

The result will be a single order:

Note that the returned order refers to 2022
Note that the returned order refers to 2022

The above query is equivalent to this SQL BETWEEN query:

Copy
        
1 SELECT * 2 FROM Orders 3 WHERE OrderDate >= '2022-01-01 00:00:00' AND OrderDate < '2023-01-01 00:00:00';

However, the DATEPART query is much easier to understand and read.

Aggregate Data by Time Periods

Since DATEPART can be used in the GROUP BY clause, it represents a great ally when it comes to aggregating data by time periods. For instance, you may be interested in retrieving the number of products sold by quarter in a year.

You could achieve that as below:

Copy
        
1 SELECT 2 DATEPART(year, OrderDate) AS Year, 3 DATEPART(quarter, OrderDate) AS Quarter, 4 SUM(Quantity) AS TotalProducts 5 FROM 6 Orders 7 GROUP BY 8 DATEPART(year, OrderDate), 9 DATEPART(quarter, OrderDate) 10 ORDER BY Year, Quarter;

Execute it, and you will get the following result:

Aggregating sales data by quarter
Aggregating sales data by quarter

Aggregating sales data by quarter

The returned data is useful for sales analysis and producing accurate reports.

Identifying Weekends

Sometimes you are interested in identifying data that meet very specific temporal criteria. For example, you might be interested in classifying orders based on whether they were placed over the weekend or not.

Considering that SQL Server weeks start on Sundays (day 1) and ends on Saturdays (day 7), you could achieve that with the SQL Server DATEPART query below:

Copy
        
1 SELECT DISTINCT CustomerId, OrderNumber, 2 CASE 3 WHEN DATEPART(weekday, OrderDate) IN (1, 7) THEN 'Weekend' 4 ELSE 'Weekday' 5 END AS DayType 6 FROM Orders;

The DISTINCT keyword is required to remove duplicate rows, as the specified SELECT list can produce duplicate records. Then, we distinguish between weekend days and weekdays with an SQL CASE statement.

The result of that query is:

Note the distinction between weekend and weekday
Note the distinction between weekend and weekday

Awesome, you are now an SQL DATEPART expert!

Best Practices of the DATEPART SQL Server Function

Here are some best practices for using the DATEPART SQL Server function like a pro:

  • Make sure you understand the date and time data types exposed by SQL Server, since each with its own characteristics and date parts.
  • DATEPART does not accept user-defined datepart arguments.
  • Do not forget that SQL Server extracts missing date parts from the 1900-01-01 00:00:00.000000000 +00:00 default dates.
  • Consider alternatives like DATEDIFF, DATEADD, or FORMAT when they better suit your needs. That is especially useful when performing SQL add to date operations.
  • DATEPART can impact performance, especially when used on large datasets. Avoid using it excessively in queries with heavy processing requirements.

Conclusion

In this guide, you understood what the SQL DATEPART function is, how it works, and when to use it. Now you know that it helps you extract the integer parts composing a date in T-SQL. Thanks to the examples shown here, you also learned when and how to use it in SQL Server.

Using native functions becomes easier with a database client that fully supports several SQL dialects. DbVisualizer is a powerful database client for SQL Server and other DBMS technologies that comes with query optimization functionality, ERD-like schema export capabilities, and more. Try DbVisualizer for free today!

FAQ

How to use DATEPART in MySQL?

MySQL does not provide the DATEPART function as in T-SQL. At the same time, you can achieve something similar through DATE_FORMAT. This MySQL function allows you to pass one or more specifiers—such as %Y for year, %m for month, and %d for day—to format a date according to the specified parts.

What input data types does the SQL DATEPART function accept?

The SQL DATEPART function accepts a date in the following data types in SQL Server:

  • date
  • datetime
  • datetimeoffset
  • datetime2
  • smalldatetime
  • time

How does the Snowflake DATE_PART function work?

The Snowflake DATE_PART function extracts the specified date or time part from a date, time, or timestamp. It takes two arguments: the part to extract (e.g., 'year', 'month', 'day') and the date or timestamp column/literal.

How does the Postgres DATE_PART function work?

The Postgres DATE_PART function extracts specific parts of a timestamp or interval value. It takes two arguments: the part to extract (e.g., 'hour', 'month', 'day') and the timestamp or interval expression to extract data from.

What are the default date part values for the DATEPART SQL Server function?

This is a table outlining the default date part values for the DATEPART function in SQL Server:

datepartDefault Value
year, yy, or yyyy1900
quarter, qq, or q1
month, mm, or m1
dayofyear, dy, or y1
day, dd, or d1
week, wk, or ww1
weekday, or dw2
hour, or hh0
minute, mi, or n0
second, ss, or s0
millisecond, or ms0
microsecond, or mcs0
nanosecond, or ns0
iso_week, isowk, or isoww1
Dbvis download link img
About the author
Antonello Zanini

Antonello is a software engineer, and often refers to himself as a technology bishop. His mission is to spread knowledge through writing.

The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

A Complete Guide to the SQL Server FOR XML PATH Clause

author Antonello Zanini tags SQL SERVER XML 8 min 2024-10-01
title

SQL OFFSET: Skipping Rows in a Query

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-09-30
title

SQL FETCH: Retrieving Data In Database Cursors

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 8 min 2024-09-24
title

A Complete Guide to the SQL Server COALESCE Function

author Antonello Zanini tags SQL SERVER 6 min 2024-09-23
title

The Definitive Guide to the NULL SQL Server Value

author Antonello Zanini tags SQL SERVER 7 min 2024-09-12
title

Index Creation in SQL

author Lukas Vileikis tags MySQL POSTGRESQL SQL SERVER 7 min 2024-08-15
title

SQL STUFF: Insert a String Into Another in SQL Server

author Antonello Zanini tags SQL SERVER 7 min 2024-07-18
title

SQL Alias: Everything You Need to Know About AS in SQL

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-07-15
title

How to Compare SQL Dates

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 4 min 2024-07-11
title

Primary Key vs. Foreign Key: A Complete Comparison

author Lukas Vileikis tags MySQL POSTGRESQL SQL SQL SERVER 7 min 2024-06-20

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗