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 CurrentMonthAbove, 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 WeekIn 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;
Want to check how much you know SQL Server?