SQL DATEPART function will extract or display specified date part from the existing date. This Sql Server Datepart function always return integer value. For example, If you want to extract year, month or quarter from the existing Date, you can use this SQL Datepart function. The syntax of the Sql Server Datepart function
DATEPART (Datepart, Date)
Date: Please specify the valid date as second argument. It can be column, expression or any SQL Server variable. The Datepart is the part of a given date which we are going to display as output. The list of available datepart argument in SQL Server.
Datepart | Abbreviations | Description |
---|---|---|
year | yy,yyyy | This will display the year value from the given date |
quarter | qq, q | This Sql Server Datepart find and display the quarter number from a given date |
month | mm, m | This will find and display the month number from the given date |
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 Sql Server datepart function will return the Week number from the specified date |
weekday | dw, w | Week days number from the given date (0 as Sunday & 6 as Saturday) |
hour | hh | It will print the Hour value. |
minute | mi, n | The Minute Value in a given date |
second | ss, s | Seconds Value present in a date |
millisecond | ms | Milliseconds Value in a given date |
microsecond | mcs | Microsecond Value present in the given date |
nanosecond | ns | Prints the Nanoseconds Value. |
TZOffset | tz | Prints the Time Zone Offset Value. |
ISO_WEEK | isowk, isoww | This will return the ISO Week Number from a given date |
SQL DATEPART Function Example 1
In this Sql Server Datepart example we are going to declare a variable of datetime2 data type. Let us assign valid date to that variable and perform all the available datepart operation
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)
We declared one variable and assigned the date and time to that SQL Server variable
DECLARE @Date datetime2 = '2015-08-25 14:24:04.1234567'
The Datepart function prints the year value.
SELECT 'YEAR', DATEPART(year, @Date)
The Datepart function print the Month number
SELECT 'MONTH', DATEPART(month, @Date)
DATEPART Function Example 2
In this example, we use one of the custom table to perform SQL Datepart operations on 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 [SQL Tutorial].[dbo].[Employee]
It will display the Year value from the Hire Date Column
DATEPART(year, [HireDate]) AS [YEAR]
The below Date Function statement will display the Quarter value from the Hire Date Column
DATEPART(quarter, [HireDate]) AS [YEAR]