Understanding the SQL DATEPART Function
James Reed
Infrastructure Engineer · Leapcell

Key Takeaways
- The SQL
DATEPART
function extracts specific parts from date or time values as integers. DATEPART
is widely used for filtering, grouping, and analyzing temporal data.- Different
datepart
arguments provide flexibility in handling date and time.
The DATEPART
function in SQL Server is a powerful tool that allows you to extract specific components from a date or time value, such as the year, month, day, hour, minute, or second. This function is particularly useful for data analysis, reporting, and time-based filtering.
What is DATEPART?
The DATEPART
function returns an integer representing a specified part of a date. Its general syntax is:
DATEPART(datepart, date)
datepart
: The part of the date to return (e.g., year, month, day).date
: The date expression from which to extract the specified part.
For example:
SELECT DATEPART(year, '2025-05-21') AS YearPart; -- Returns: 2025
Supported datepart
Arguments
Here are some commonly used datepart
arguments:
datepart | Abbreviations | Description | |
---|---|---|---|
year | yy, yyyy | Year | |
quarter | qq, q | Quarter of the year | |
month | mm, m | Month | |
dayofyear | dy, y | Day of the year | |
day | dd, d | Day of the month | |
week | wk, ww | Week number | |
weekday | dw, w | Day of the week | |
hour | hh | Hour | |
minute | mi, n | Minute | |
second | ss, s | Second | |
millisecond | ms | Millisecond | |
microsecond | mcs | Microsecond | |
nanosecond | ns | Nanosecond | |
tzoffset | tz | Time zone offset in minutes | |
iso_week | isowk, isoww | ISO 8601 week number |
Note: The DATEPART
function returns an integer value corresponding to the specified part of the date.
Practical Examples
Extracting Specific Date Parts
SELECT DATEPART(year, '2025-05-21') AS YearPart, DATEPART(month, '2025-05-21') AS MonthPart, DATEPART(day, '2025-05-21') AS DayPart; -- Returns: 2025, 5, 21
Filtering Records by Year
SELECT * FROM Orders WHERE DATEPART(year, OrderDate) = 2025;
Grouping Data by Month
SELECT DATEPART(month, OrderDate) AS OrderMonth, COUNT(*) AS TotalOrders FROM Orders GROUP BY DATEPART(month, OrderDate);
Identifying Weekends
SELECT OrderID, OrderDate, CASE WHEN DATEPART(weekday, OrderDate) IN (1, 7) THEN 'Weekend' ELSE 'Weekday' END AS DayType FROM Orders;
Considerations
-
First Day of the Week: The value returned by
DATEPART(weekday, date)
depends on the setting ofSET DATEFIRST
, which specifies the first day of the week. For example, in the U.S., Sunday is typically considered the first day of the week (SET DATEFIRST 7
). -
ISO Week Numbering: The
iso_week
datepart follows the ISO 8601 standard, where the first week of the year is the one with the first Thursday. This can result in different week numbers compared to the standardweek
datepart. -
Time Zone Offset: When using
DATEPART(tzoffset, date)
, the function returns the time zone offset in minutes. This is particularly useful when working withdatetimeoffset
data types.
Conclusion
The DATEPART
function is an essential tool in SQL Server for dissecting date and time values into their constituent parts. Whether you're filtering data by specific time frames, grouping records for analysis, or formatting dates for reporting, DATEPART
provides the flexibility needed to handle various temporal data scenarios effectively.
FAQs
It extracts specific components, like year, month, or day, from a date or time value.
Yes, you can use DATEPART to filter data by any date part, such as year or month.
Yes, it supports both standard and ISO week numbering using different arguments.
We are Leapcell, your top choice for hosting backend projects.
Leapcell is the Next-Gen Serverless Platform for Web Hosting, Async Tasks, and Redis:
Multi-Language Support
- Develop with Node.js, Python, Go, or Rust.
Deploy unlimited projects for free
- pay only for usage — no requests, no charges.
Unbeatable Cost Efficiency
- Pay-as-you-go with no idle charges.
- Example: $25 supports 6.94M requests at a 60ms average response time.
Streamlined Developer Experience
- Intuitive UI for effortless setup.
- Fully automated CI/CD pipelines and GitOps integration.
- Real-time metrics and logging for actionable insights.
Effortless Scalability and High Performance
- Auto-scaling to handle high concurrency with ease.
- Zero operational overhead — just focus on building.
Explore more in the Documentation!
Follow us on X: @LeapcellHQ