SQL Server DATEPART() Function

DATEPART(datepart, date) returns an integer for a specified part of a date or time value. Use DATENAME() when you need a name such as a month or weekday instead of a number. The datepart argument must be a supported literal; it cannot be supplied as a variable or quoted string. See Microsoft’s DATEPART documentation.

Syntax

DATEPART(datepart, date)

Parameters

• datepart: A supported part such as year, month, day, hour, minute, or second. SQL Server also accepts documented abbreviations.
• date: A date or time expression such as date, datetime, datetime2, datetimeoffset, smalldatetime, or time.

The return value is an integer. Language and date-format settings can affect results when date is a string literal; typed date/time values avoid string-parsing ambiguity.

Examples

Extract the year and month

DECLARE @created_at datetime2 = '2022-03-11T15:30:45';

SELECT DATEPART(year, @created_at) AS year_number,
       DATEPART(month, @created_at) AS month_number;

Result:

Extract the hour, minute, and second

SELECT DATEPART(hour, @created_at) AS hour_number,
       DATEPART(minute, @created_at) AS minute_number,
       DATEPART(second, @created_at) AS second_number;

Result:

For text such as the month name, use DATENAME(). For ODBC-compatible escape syntax that extracts time parts, see the SQL Server ODBC HOUR(), MINUTE(), and SECOND() pages.