SQL Server DATEPART() Function (2023)

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


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

SQL Server DATEPART() Function (1)

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:

SQL Server DATEPART() Function (2)

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;
SQL Server DATEPART() Function (3)

Want to check how much you know SQL Server?

Top Articles
Latest Posts
Article information

Author: Velia Krajcik

Last Updated: 14/09/2023

Views: 6187

Rating: 4.3 / 5 (74 voted)

Reviews: 81% of readers found this page helpful

Author information

Name: Velia Krajcik

Birthday: 1996-07-27

Address: 520 Balistreri Mount, South Armand, OR 60528

Phone: +466880739437

Job: Future Retail Associate

Hobby: Polo, Scouting, Worldbuilding, Cosplaying, Photography, Rowing, Nordic skating

Introduction: My name is Velia Krajcik, I am a handsome, clean, lucky, gleaming, magnificent, proud, glorious person who loves writing and wants to share my knowledge and understanding with you.