SQL Server DATEPART Function

In SQL Server, the DATEPART() function returns an integer representing the specific part of the given date e.g. day, month, year, hour, seconds, etc.

The DATEPART() and DATENAME() are similar except for the return type. DATEPART returns int and DATENAME returns nvarchar.

DATEPART (datepart, date)


datepart: The specific part of the date parameter for which the DATEPART() function will return an integer. The following table lists all valid datepart values:

datepart datepart abbreviation
year y, yy, yyyy
quarter qq, q
month mm, m
dayofyear dy
day dd, d
week wk, ww
hour hh
minute mi, n
second ss, s
millisecond ms
microsecond mcs
nanosecond ns
tzoffset tz
iso_week isowk, isoww

date: A date or date string that can be resolved to one of the data types: date, datetime, datetime2, time, smalldatetime, or datetimeoffset. It can be a string, a user-defined variable, or a table column.

Return Value

DATEPART returns an integer representing the datepart of a given date.

Get Month of Date

In the following example, the DATEPART() function returns the month of the given date as an integer.

Example: Get Current Month Number
DATEPART(mm, GETDATE()) AS CurrentMonth

Above, the DATEPART() function returns the month number of the current date. The GETDATE() function returns the current datetime value.

Query Result in SSMS

Get Different Parts of the Date

In the following example, the DATEPART() method returns all the datepart values of the given date.

Example: DATEPART() Function
SELECT DATEPART (mm, '04/20/2021 9:45:52.564722') AS Month
,DATEPART (d, '04/20/2021 9:45:52.564722') AS Day
,DATEPART (yy, '04/20/2021 9:45:52.564722') AS Year
,DATEPART (hh, '04/20/2021 9:45:52.564722') AS Hour
,DATEPART (minute, '04/20/2021 9:45:52.564722') AS Minute
,DATEPART (s, '04/20/2021 9:45:52.564722') AS Second
,DATEPART (ms, '04/20/2021 9:45:52.564722') AS MilliSecond
,DATEPART (mcs, '04/20/2021 9:45:52.564722') AS MicroSecond
,DATEPART (q, '04/20/2021 9:45:52.564722') AS Quarter
,DATEPART (dayofyear, '04/20/2021 9:45:52.564722') AS DayOfYear
,DATEPART (wk, '04/20/2021 9:45:52.564722') AS Week

In the above example, we passed different datepart values in the DATEPART() function. It will display the following result:

Query Result in SSMS

Use DATEPART with Column

In the following example, the DATEPART function is used with the HireDate column of the Employee table and returns the Quarter and Year of hiring employees.

Example: DATEPART() with Column
SELECT EmployeeID, DATEPART(q, HireDate) AS QuarterHired, 
DATEPART(yy, HireDate) AS YearHired FROM Employee;
Query Result in SSMS
Want to check how much you know SQL Server?