Learn how to use the SQL DATEPART function in SQL Server to extract and return an integer 1️⃣ value for a specific part of a date
SQL DATEPART Function
The DATEPART function in SQL Server returns an integer value representing the specified part of a given date/time value. It is useful for extracting components of dates and times to use for filtering, grouping, calculations, and more.
Syntax
The syntax for DATEPART is:
DATEPART(datepart, date)It takes two parameters:
- datepart – The specific part of the date to return such as year, month, day, hour, minute, etc.
- date – The input date/time value. This can be a literal string, column value, variable, or expression of date/time data types like datetime or smalldatetime.
Datepart Parameter
The datepart parameter specifies which part of the date to return. Valid options include:
- Year – yyyy, yy
- Quarter – qq, q
- Month – mm, m
- Day of Year – dy, y
- Day – dd, d
- Week – wk, ww
- Weekday – dw
- Hour – hh
- Minute – mi, n
- Second – ss, s
- Millisecond – ms
- Microsecond – mcs
- Nanosecond – ns
- Timezone Offset – tz
- ISO Week – isowk, isoww
You can use either the full name or abbreviation specified above. User-defined values are not allowed for the datepart parameter.
Date Parameter
The date parameter can be specified as:
- A literal string in a supported date format like ‘2023-01-15’
- A column name with a date/time data type like datetime
- A variable of a date/time data type like @mydatetime
- An expression that evaluates to a date/time like GETDATE()
Supported data types include datetime, smalldatetime, datetime2, datetimeoffset, time and date.
Return Value
DATEPART returns an integer representing the value of the specified part of the input date.
The return value does not depend on the language environment or dateformat settings. The full name datepart and its abbreviation return the same integer value.
Function Usage
The DATEPART function is commonly used to:
- Extract components of a date/time value like year, month, day, hour etc.
- Filter datetime values based on parts like year, month, weekday etc.
- Group data by time components like year, week, month etc.
- Calculate time/date differences and durations
It provides an efficient way to retrieve and work with portions of date/time values in SQL Server.
Examples
Get date parts from date literal
SELECT DATEPART(yy, '2023-01-15') AS Year, DATEPART(mm, '2023-01-15') AS Month, DATEPART(dd, '2023-01-15') AS DayThis returns:
Year Month Day 2023 1 15Get date parts from column
SELECT DATEPART(yy, OrderDate) AS OrderYear, DATEPART(mm, OrderDate) AS OrderMonth FROM Sales.OrdersThis returns the year and month for each order date in the Orders table.
Group by datepart
SELECT DATEPART(yy, OrderDate) AS OrderYear, COUNT(OrderID) AS TotalOrders FROM Sales.Orders GROUP BY DATEPART(yy, OrderDate) ORDER BY OrderYearThis groups orders by year and returns order totals for each year.
Filter by datepart
SELECT * FROM Production.Products WHERE DATEPART(mm, SellStartDate) = 2This returns products with a sell start month of February.
Week and Weekday Dateparts
For the week and weekday dateparts, the return value depends on the value configured with SET DATEFIRST. This setting specifies the first day of the week from 1 = Monday to 7 = Sunday. Sunday is the default.
So if DATEFIRST is set to 2, DATEPART(dw, ‘2023-01-15’) would return 2 meaning Tuesday.
TZOffset Datepart
The tzoffset datepart returns the timezone offset of the input datetime value from UTC in minutes.
Examples:
DATEPART(tz, '2023-01-15 01:00:00 -08:00') -- returns 480DATEPART(tz, '2023-01-15 01:00:00 +05:30') -- returns -330Default Values
If the datepart specified does not exist in the input date value, DATEPART returns default values:
- Year – 1900
- Month – 1
- Day – 1
So DATEPART(yy, ’12:30:00′) would return 1900 as the default year.
For invalid date strings, it returns NULL.
ISO Week Datepart
The iso_week datepart returns the ISO 8601 week number of the year for the input date. ISO 8601 defines a numbering system for weeks where each week starts on a Monday and ends on a Sunday.
The first ISO week of a year contains the first Thursday of the year. Some examples:
DATEPART(isowk, '2023-01-01') -- returns 1DATEPART(isowk, '2023-01-08') -- returns 2fractional seconds
When the input date has fractional seconds, DATEPART will return the full fractional component:
SELECT DATEPART(second, '2022-12-31 23:59:59.12345') -- returns 59.12345 SELECT DATEPART(millisecond, '2022-12-31 23:59:59.12345') --returns 123smalldatetime
If the input date is smalldatetime, seconds will be truncated:
SELECT DATEPART(second, '2023-01-15 13:30:59') -- returns 59 SELECT DATEPART(second, CAST('2023-01-15 13:30:59' AS smalldatetime)) -- returns 0Null Values
If NULL is passed for either parameter, DATEPART returns NULL:
SELECT DATEPART(month, NULL) -- returns NULLSummary
The DATEPART SQL function serves as a powerful tool in SQL Server for efficiently extracting specific parts of date and time values as integers. Its primary purpose is to break down date and time information into individual components, such as year, month, day, hour, minute, and second. This decomposition of temporal data is incredibly valuable, as it enables users to perform a wide range of operations, including filtering, grouping, and various calculations.
When it comes to filtering and grouping data, the DATEPART function plays a pivotal role. It allows database administrators and developers to categorize and analyze data based on the date or time components they extract. For instance, you can effortlessly group data by months or years, making it easier to generate insightful reports and gain a better understanding of your dataset.
One important consideration when working with DATEPART is its behavior when dealing with null values. If the input date or time value is null, DATEPART returns a default value of zero. This behavior is essential to keep in mind while crafting SQL queries, as it can impact the results you obtain. Ensuring your queries account for null values and their handling can help you produce more accurate and reliable outcomes.
Additionally, it’s crucial to be aware of the DATEFIRST setting in SQL Server. This setting determines the first day of the week and influences the numbering of weekdays. Depending on the specific requirements of your applications, you may need to adjust the DATEFIRST setting to ensure that week numbering aligns with your expectations. This is particularly significant in scenarios where week numbering plays a critical role in data analysis or reporting.
In summary, the DATEPART SQL function is a versatile and indispensable tool in SQL Server. It simplifies the manipulation of date and time data, making it easier to filter, group, and perform calculations on temporal information. Understanding how it handles null values and the impact of the DATEFIRST setting is essential for using it effectively in your database operations.
