intro
Let's learn everything you need to know about the SQL DATEPART function to extract specific components from a date in T-SQL.
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:
1
DATEPART(datepart, date)
Where:
The datepart argument accepts only the following fixed values:
| datepart | Explanation |
|---|---|
| year, yy, or yyyy | Extracts the year from the date |
| quarter, qq, or q | Extracts the quarter of the year from the date (1 to 4) |
| month, mm, or m | Extracts the month from the date (1 to 12) |
| dayofyear, dy, or y | Extracts the day of the year from the date (1 to 366) |
| day, dd, or d | Extracts the day of the month from the date (1 to 31) |
| week, wk, or ww | Extracts the week number of the year from the date |
| weekday, or dw | Extracts the day of the week from the date (1 to 7) |
| hour, or hh | Extracts the hour from the time (0 to 23) |
| minute, mi, or n | Extracts the minute from the time (0 to 59) |
| second, ss, or s | Extracts the second from the time (0 to 59) |
| millisecond, or ms | Extracts the millisecond from the time (0 to 999) |
| microsecond, or mcs | Extracts the microsecond from the time |
| nanosecond, or ns | Extracts the nanosecond from the time |
| tzoffset, or tz | Extracts the time zone offset from UTC |
| iso_week, isowk, or isoww | Extracts 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:
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:
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:
You can test that with the following SQL Server DATEPART query:
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;
1
1900-01-01 00:00:00.000000000 +00:00
In other words, consider the query below:
1
SELECT DATEPART(year, '16:18')
That will return:
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 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:
1
SELECT *
2
FROM Orders
3
WHERE DATEPART(year, OrderDate) = 2022;
The result will be a single order:
The above query is equivalent to this SQL BETWEEN query:
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:
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
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:
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:
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:
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:
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:
| datepart | Default Value |
|---|---|
| year, yy, or yyyy | 1900 |
| quarter, qq, or q | 1 |
| month, mm, or m | 1 |
| dayofyear, dy, or y | 1 |
| day, dd, or d | 1 |
| week, wk, or ww | 1 |
| weekday, or dw | 2 |
| hour, or hh | 0 |
| minute, mi, or n | 0 |
| second, ss, or s | 0 |
| millisecond, or ms | 0 |
| microsecond, or mcs | 0 |
| nanosecond, or ns | 0 |
| iso_week, isowk, or isoww | 1 |
