DATEPART (Transact-SQL)

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

Syntax

DATEPART ( datepart , date )

Parameters

Parameter Description Required
datepart The part of the date that you want to return. This parameter can be any valid datepart identifier. The following table lists all valid datepart arguments. Yes
date The expression that resolves to a date, time, or datetime value, or a character string in a date format. For more information about the formats that are considered valid date formats, see Supported Date and Time Formats (Database Engine). Yes

Return Value

INT. Returns an integer value that corresponds to the specified datepart.

Datepart Arguments

The following table lists all valid datepart arguments and their abbreviations.

Datepart Abbreviation Description
year yy, yyyy Year
quarter qq, q Quarter of the year. The quarter is determined by the date's month and is returned in the range of 1 through 4.
month mm, m Month
dayofyear dy, y Day of the year
day dd, d Day of the month
week wk, ww Week of the year. The week number returned by DATEPART(week, date) is the week number defined by the ISO 8601 standard. This standard is different from the week number returned by the @@DATEFIRST setting.
hour hh Hour
minute mi, n Minute
second ss, s Second
millisecond ms Millisecond
microsecond mcs Microsecond
nanosecond ns Nanosecond
weekday dw, w Day of the week. DATEPART(weekday, date) returns a value corresponding to the @@DATEFIRST setting. The default is 7 for Sunday.

Remarks

DATEPART returns a value of data type INT.

DATEPART is equivalent to the DATENAME function, except that DATENAME returns the date part as a character string. DATEPART returns the date part as an integer.

The return value of DATEPART(weekday, date) depends on the value of the `@@DATEFIRST` setting.

Note: The week (wk, ww) datepart follows the ISO 8601 standard. This means that week 1 is the first week with a Thursday in it. This differs from the U.S. standard where week 1 is the week that contains January 1st.

Examples

Getting the year from a date

SELECT DATEPART(year, '2023-10-27');

Result:

2023

Getting the month and day from a date

SELECT DATEPART(month, '2023-10-27') AS MonthNumber, DATEPART(day, '2023-10-27') AS DayNumber;

Result:

MonthNumber  DayNumber
-----------  ---------
10           27

Getting the hour and minute from a time value

SELECT DATEPART(hour, '14:30:15.123') AS HourValue, DATEPART(minute, '14:30:15.123') AS MinuteValue;

Result:

HourValue  MinuteValue
---------  -----------
14         30

Getting the day of the week (default @@DATEFIRST setting is 7 for Sunday)

SELECT DATEPART(weekday, '2023-10-27');

Result (if Sunday is 1):

6

(Assuming Friday is the 6th day of the week)

See Also