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)

Parameters

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

SELECT GETDATE() AS Today, 
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.

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:

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;