DATEPART (SQL Server)

Returns an integer representing the specified datepart of the specified date.

Syntax

DATEPART ( datepart , date )

Parameters

datepart
The component of the date that is to be returned. For a list of all valid datepart arguments, see the "Remarks" section.
date
An expression that can be resolved to a date, time, datetime, datetime2, or smalldatetime value.

Return Value

INT

Description

The DATEPART function supports standard date and time components. The value returned depends on the datepart argument specified.

Valid datepart arguments:

Abbreviation(s) Value Range
year, yy, yyyy Year 1753 through 9999
quarter, qq, q Quarter of the year (1-4) 1 through 4
month, mm, m Month 1 through 12
day, dd, d Day of the month 1 through 31
week, wk, ww Week of the year 1 through 53
weekday, dw, w Day of the week (depends on DATEFIRST setting) 1 through 7
hour, hh, h Hour 0 through 23
minute, mi, n Minute 0 through 59
second, ss, s Second 0 through 59
millisecond, ms Millisecond 0 through 999
microsecond, mcs Microsecond 0 through 999999
nanosecond, ns Nanosecond 0 through 999999999

The interpretation of week and weekday depends on the SET DATEFIRST setting.

Note: The weekday value for a specific day can vary based on the server's regional settings and the DATEFIRST setting.

Examples

Example 1: Get the month from a date

SELECT DATEPART(month, '2023-10-26 14:30:00');
-- Returns: 10

Example 2: Get the day of the week

SELECT DATEPART(weekday, GETDATE());
-- Returns the day of the week for the current date (e.g., 4 if Thursday)

Example 3: Get the hour from a datetime2 value

SELECT DATEPART(hour, CAST('2023-11-15 08:15:45.123' AS DATETIME2));
-- Returns: 8

Tip: You can use the DATENAME function to return the name of a date part (e.g., 'October' instead of 10).

See Also