SQL Server DATEPART: Extracting Date and Time Parts Effectively

In SQL Server, the DATEPART function is invaluable for dissecting date and time values. It allows you to extract a specific component, known as a datepart, from a given date, returning it as an integer. This function is crucial for data analysis, reporting, and various date-related calculations within your SQL Server databases. Understanding and utilizing DATEPART effectively can significantly enhance your ability to manipulate and interpret date and time data.

This article delves into the intricacies of the SQL Server DATEPART function, providing a comprehensive guide to its syntax, arguments, return values, and practical applications. Whether you’re a seasoned database administrator or a developer new to SQL Server, this guide will equip you with the knowledge to leverage DATEPART for your data manipulation needs.

Understanding the Syntax of DATEPART

The syntax for the DATEPART function is straightforward, making it easy to use in your SQL queries:

DATEPART ( datepart , date )

Let’s break down each component of this syntax:

datepart: This is the essential argument that specifies which part of the date you want to extract. It must be a valid datepart argument recognized by SQL Server. We’ll explore the valid datepart arguments in detail in the next section. Crucially, DATEPART does not accept user-defined variables for the datepart argument; it must be a literal value.
date: This argument represents the date or time value from which you want to extract the specified datepart. It can be any expression that resolves to one of the supported date and time data types in SQL Server. These data types include:
- date
- datetime
- datetimeoffset
- datetime2
- smalldatetime
- time
The date argument can be a column expression, a literal value, another expression, or a user-defined variable. When providing date literals, it’s best practice to use four-digit years to avoid any ambiguity.

Valid Datepart Arguments in SQL Server

The power of DATEPART lies in its versatility to extract various components of a date. Here’s a table outlining the valid datepart arguments you can use with the function, along with their abbreviations:

Datepart	Abbreviations	Description
year	yy, yyyy	Year
quarter	qq, q	Quarter of the year (1-4)
month	mm, m	Month of the year (1-12)
dayofyear	dy, y	Day of the year (1-366)
day	dd, d	Day of the month (1-31)
week	wk, ww	Week of the year (1-53)
weekday	dw	Day of the week (Sunday=1, Saturday=7 by default)
hour	hh	Hour (0-23)
minute	mi, n	Minute (0-59)
second	ss, s	Second (0-59)
millisecond	ms	Millisecond (0-999)
microsecond	mcs	Microsecond (0-999999)
nanosecond	ns	Nanosecond (0-999999999)
tzoffset	tz	Time zone offset in minutes
iso_week	isowk, isoww	ISO week of the year (1-53)

Understanding the various datepart arguments is key to effectively using the DATEPART function in SQL Server.

As you can see, DATEPART offers a wide range of options to extract date and time components, from broad categories like ‘year’ and ‘month’ to more granular details like ‘millisecond’ and ‘time zone offset’. Both the full name and the abbreviations for each datepart are valid and will produce the same result.

Return Type and Values of DATEPART

The DATEPART function consistently returns an int (integer) value. The specific integer value returned depends on the datepart you specify and the date value you provide.

The behavior of DATEPART can be influenced by the language settings configured in SQL Server using SET LANGUAGE and the default language server configuration. Additionally, if the date argument is a string literal in certain formats, the SET DATEFORMAT setting can also affect the result. However, it’s important to note that SET DATEFORMAT does not impact the return value when the date is a column expression of a date or time data type.

Let’s illustrate the return values with an example. Consider the following SQL statement:

SELECT DATEPART(datepart,'2007-10-30 12:15:32.1234567 +05:10');

This statement uses the date value '2007-10-30 12:15:32.1234567 +05:10', which is a datetimeoffset(7) data type. The table below shows the return value for different datepart arguments when applied to this date:

Datepart	Return Value
year, yyyy, yy	2007
quarter, qq, q	4
month, mm, m	10
dayofyear, dy, y	303
day, dd, d	30
week, wk, ww	44
weekday, dw	3
hour, hh	12
minute, n	15
second, ss, s	32
millisecond, ms	123
microsecond, mcs	123456
nanosecond, ns	123456700
tzoffset, tz	310
iso_week, isowk, isoww	44

Notice that for the nanosecond datepart, the last two digits are always 00, and the value has a scale of 9, representing .123456700.

Week and Weekday Dateparts: Understanding DATEFIRST

When working with the week (wk, ww) or weekday (dw) datepart arguments, it’s crucial to understand the impact of the SET DATEFIRST setting. DATEFIRST determines the first day of the week, which in turn affects how weeks are numbered and which day is considered the first day of the week.

For both week and weekday, the DATEPART return value is sensitive to the DATEFIRST setting. By default, in U.S. English, SET DATEFIRST 7 (Sunday) is the default.

January 1st of any year is always considered the starting point for week numbering. Therefore:

DATEPART(wk, 'Jan 1, xxxx') = 1

where xxxx represents any year.

Let’s examine how DATEFIRST influences the week and weekday values for the date '2007-04-21'. January 1, 2007, was a Monday, and April 21, 2007, was a Saturday. The following table illustrates the return values for different SET DATEFIRST arguments:

SET DATEFIRST Argument	Week Returned	Weekday Returned
1 (Monday)	16	6
2 (Tuesday)	17	5
3 (Wednesday)	17	4
4 (Thursday)	17	3
5 (Friday)	17	2
6 (Saturday)	17	1
7 (Sunday)	16	7

To obtain these values after setting DATEFIRST, you can use the following SQL statement:

SELECT DATEPART(week, '2007-04-21 '), DATEPART(weekday, '2007-04-21 ')

The SET DATEFIRST setting significantly alters the results of DATEPART when used with ‘week’ and ‘weekday’ dateparts.

Year, Month, and Day: Equivalents to Dedicated Functions

For extracting the year, month, and day components, DATEPART provides results identical to the dedicated functions YEAR, MONTH, and DAY respectively.

DATEPART(year, date) is equivalent to YEAR(date)
DATEPART(month, date) is equivalent to MONTH(date)
DATEPART(day, date) is equivalent to DAY(date)

While these dedicated functions offer a more concise way to extract these specific date parts, DATEPART remains a versatile option, especially when you need to extract different date parts dynamically.

ISO_WEEK: Adhering to ISO 8601 Standard

The iso_week datepart argument aligns with the ISO 8601 week-date system, an internationally recognized standard for week numbering. In this system, each week is associated with the year in which the Thursday of that week falls.

For instance, week 1 of 2004 (denoted as 2004W01) spanned from Monday, December 29, 2003, to Sunday, January 4, 2004. This system is commonly used in European countries and regions, while non-European regions may not adhere to it.

It’s important to note that the highest week number in a year can be either 52 or 53, depending on the year.

The following table highlights the variations in week numbering systems across different regions:

First Day of Week	First Week of Year Contains	Weeks Assigned Two Times	Used By/In
Sunday	January 1, First Saturday, 1-7 days of year	Yes	United States
Monday	January 1, First Sunday, 1-7 days of year	Yes	Most of Europe and the United Kingdom
Monday	January 4, First Thursday, 4-7 days of year	No	ISO 8601, Norway, and Sweden
Monday	January 7, First Monday, Seven days of year	No
Wednesday	January 1, First Tuesday, 1-7 days of year	Yes
Saturday	January 1, First Friday, 1-7 days of year	Yes

TZOFFSET: Retrieving Time Zone Offset

The tzoffset (tz) datepart is specifically designed to return the time zone offset in minutes (signed integer).

For example, the statement:

SELECT DATEPART(tzoffset, '2007-05-10 00:00:01.1234567 +05:10');

will return 310, representing a time zone offset of 310 minutes (which is 5 hours and 10 minutes).

DATEPART handles the tzoffset value differently based on the data type of the date argument:

For datetimeoffset and datetime2 data types, tzoffset returns the time offset in minutes. For datetime2, the offset is always 0 minutes as it does not store time zone information.
For data types that can be implicitly converted to datetimeoffset or datetime2, DATEPART also returns the time offset in minutes. This excludes other date/time data types that cannot be implicitly converted.
Using tzoffset with parameters of other data types will result in an error.

Handling smalldatetime Data Type

When the date argument is of the smalldatetime data type, DATEPART will always return seconds as 00. This is because smalldatetime only stores time with minute precision.

Default Return Values for Missing Dateparts

If the date argument’s data type does not inherently contain the requested datepart, DATEPART will return default values only when a literal is specified for the date.

For instance, the default year-month-day for any date data type is '1900-01-01'. Consider this example:

SELECT DATEPART(year, '12:10:30.123') ,DATEPART(month, '12:10:30.123') ,DATEPART(day, '12:10:30.123') ,DATEPART(dayofyear, '12:10:30.123') ,DATEPART(weekday, '12:10:30.123');

This statement provides a time literal as the date argument. Because a time value does not contain year, month, or day information, DATEPART returns the default date components, along with the dayofyear and weekday calculated based on the default date: 1900, 1, 1, 1, 2.

However, if the date is provided as a variable or a table column, and the data type of that variable or column lacks the specified datepart, DATEPART will raise error 9810.

For example:

DECLARE @t time = '12:10:30.123';
SELECT DATEPART(year, @t);

This code snippet will result in an error because the time data type, assigned to the variable @t, does not include the ‘year’ datepart.

Fractional Seconds with DATEPART

DATEPART is precise enough to return fractional seconds when dealing with data types that support them, such as datetime2 and datetimeoffset.

The following examples demonstrate this:

SELECT DATEPART(millisecond, '00:00:01.1234567'); -- Returns 123
SELECT DATEPART(microsecond, '00:00:01.1234567'); -- Returns 123456
SELECT DATEPART(nanosecond, '00:00:01.1234567'); -- Returns 123456700

These statements effectively extract the millisecond, microsecond, and nanosecond components from the given time value.

Remarks and Best Practices for DATEPART

Versatile Usage: DATEPART can be used in various clauses of your SQL queries, including SELECT, WHERE, HAVING, GROUP BY, and ORDER BY. This makes it highly flexible for data manipulation and analysis.
Implicit Conversion of String Literals: In SQL Server 2008 (10.0.x) and later versions, DATEPART implicitly converts string literals to the datetime2 data type. This means that when passing a date as a string, DATEPART does not support the YDM (Year-Day-Month) format. To use the YDM format, you must explicitly cast the string to datetime or smalldatetime.
Clarity and Readability: While abbreviations for datepart arguments are allowed, using the full names (e.g., year instead of yy) generally enhances the readability and maintainability of your SQL code, especially for complex queries.
Error Handling: Be mindful of error 9810, which occurs when you attempt to extract a datepart that is not supported by the data type of the date argument, especially when using variables or column expressions.

Examples of DATEPART in Action

Example 1: Extracting the Base Year

This example demonstrates how DATEPART interprets numeric date values, considering 0 as January 1, 1900:

SELECT DATEPART(year, 0), DATEPART(month, 0), DATEPART(day, 0);
-- Returns: 1900 1 1

Example 2: Retrieving the Day of the Month

This example extracts the day component from the date '12/20/1974':

-- Uses AdventureWorks database
SELECT TOP(1) DATEPART(day,'12/20/1974')
FROM dbo.DimCustomer;
-- Returns: 20

Example 3: Getting the Year from a Date

This example retrieves the year part from the date '12/20/1974':

-- Uses AdventureWorks database
SELECT TOP(1) DATEPART(year,'12/20/1974')
FROM dbo.DimCustomer;
-- Returns: 1974

Conclusion

The SQL Server DATEPART function is a powerful and versatile tool for extracting specific components from date and time values. By mastering its syntax, understanding the various datepart arguments, and considering the nuances of DATEFIRST and data type compatibility, you can effectively leverage DATEPART in your SQL queries for data analysis, reporting, and date-related calculations. Its flexibility and precision make it an indispensable function for anyone working with date and time data in SQL Server.