DATEPART (Transact-SQL)

Returns an integer that represents the specified date part of the specified date.

Syntax

DATEPART ( datepart , date )

Parameters

Parameter Description Data Type Notes
datepart Specifies the part of the date that is to be returned. The following table lists all valid values for the datepart argument. varchar or int See the list of valid values below.
date The expression that resolves to a datetime, smalldatetime, date, varchar, nvarchar, datetime2, or datetimeoffset value. datetime, smalldatetime, date, varchar, nvarchar, datetime2, or datetimeoffset If date is a character string, it must be in a format that SQL Server can recognize as a date or time.

Return Value

Returns an int.

Valid datepart Values

The following table lists all valid values for the datepart argument and their abbreviations. Note that the system recognizes the full name and the first three letters of the name, except for year and dayofyear.

datepart Abbreviation
year yy, yyyy
quarter qq, q
month mm, m
dayofyear dy, y
day dd, d
week wk, ww
hour hh
minute mi, n
second ss, s
millisecond ms
microsecond mcs
nanosecond ns
weekday dw, w

Note

dayofyear returns the ordinal day of the year. weekday returns the day of the week.

Examples

Example 1: Get the year from a date

SELECT DATEPART(year, '2023-10-27 10:30:00.123') AS YearPart;

Result:

2023

Example 2: Get the month from a date

SELECT DATEPART(month, '2023-10-27 10:30:00.123') AS MonthPart;

Result:

10

Example 3: Get the day of the week

SELECT DATENAME(weekday, '2023-10-27') AS DayOfWeekName;
SELECT DATEPART(weekday, '2023-10-27') AS DayOfWeekNumber;

Result (assuming '2023-10-27' is a Friday):

Friday
5

Tip

DATEPART(weekday, date) returns a number representing the day of the week. The default setting for @@DATEFIRST is 7 (Sunday), so Sunday is 1, Monday is 2, ..., Saturday is 7. You can change this with the SET DATEFIRST statement.

Example 4: Using abbreviations

SELECT DATEPART(yy, GETDATE()) AS CurrentYear;
SELECT DATEPART(m, GETDATE()) AS CurrentMonth;
SELECT DATEPART(dd, GETDATE()) AS CurrentDay;

See Also