SQL Server DATEPART function will extract or display the specified date part from the existing date. This SQL Datepart function always returns an integer value. So, for example, If you want to extract the year, month, or quarter from the existing Date, you can use this one.
The syntax of the SQL Datepart function is shown below.
DATEPART (Datepart, Date)
Date: Please specify the valid date as the second argument. It can be a column, an expression, or any variable. We will display the part of a given date as output.
The following is the list of available SQL Server datepart arguments.
SQL Datepart | Abbreviations | Description |
---|---|---|
year | yy,yyyy | It will display the year value from the given date. |
quarter | qq, q | It will find and display the quarter number |
month | mm, m | It will find and display the month number |
dayofyear | dy, y | Day of a year number from the user given date (1 to 365) |
day | dd, d | Day number from a given date (1 to 31) |
week | wk, ww | This function will return the Week number |
weekday | dw, w | Weekdays number from the given date (0 as Sunday & 6 as Saturday) |
hour | hh | Prints the Hour value. |
minute | mi, n | The Minute Value |
second | ss, s | Seconds Value |
millisecond | ms | Milliseconds Value |
microsecond | mcs | Microsecond Value present in it |
nanosecond | ns | Prints the Nanoseconds Value. |
TZOffset | tz | Prints the Time Zone Offset Value. |
ISO_WEEK | isowk, isoww | Returns the ISO Week Number |
SQL Server DATEPART Function Example
In this SQL DATEPART example, we will declare a variable of datetime2 data type. Then, let us assign a valid date to that variable and perform all the available operations.
We declared one variable and assigned the date and time to that SQL Server variable. Here, the first select statement prints the year value, and the next one prints the month number.
DECLARE @Date datetime2 = '2015-08-25 14:24:04.1234567' SELECT 'YEAR' AS [DatePart], DATEPART(year, @Date) AS [New Date] UNION ALL SELECT 'QUARTER', DATEPART(quarter, @Date) UNION ALL SELECT 'MONTH', DATEPART(month, @Date) UNION ALL SELECT 'DAYOFYEAR', DATEPART(dayofyear, @Date) UNION ALL SELECT 'DAY', DATEPART(day, @Date) UNION ALL SELECT 'WEEK', DATEPART(week, @Date) UNION ALL SELECT 'WEEKDAY', DATEPART(weekday, @Date) UNION ALL SELECT 'HOUR', DATEPART (hour, @Date) UNION ALL SELECT 'MINUTE', DATEPART(minute, @Date) UNION ALL SELECT 'SECOND', DATEPART(second, @Date) UNION ALL SELECT 'MILLISECOND', DATEPART(millisecond, @Date) UNION ALL SELECT 'MICROSECOND', DATEPART(microsecond, @Date) UNION ALL SELECT 'NANOSECOND',DATEPART(nanosecond, @Date)

SQL DATEPART Example 2
In this example, we use one of the custom tables to perform Datepart operations on the Hire Date column.
SELECT [FirstName] + ' '+ [LastName] AS [Full Name] ,[Occupation] ,[YearlyIncome] ,[HireDate] ,DATEPART(year, [HireDate]) AS [YEAR] ,DATEPART(quarter, [HireDate]) AS [QUARTER] ,DATEPART(month, [HireDate]) AS [MONTH] ,DATEPART(day, [HireDate]) AS [DAY] FROM [Employee]

It will display the Year value from the HireDate Column.
DATEPART(year, [HireDate]) AS [YEAR]
The below Date Function statement will display the Quarter value from the HireDate Column.
DATEPART(quarter, [HireDate]) AS [YEAR]