SQL DATEPART

SQL Server DATEPART function will extract or display the specified date part from the existing date. This 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 and the syntax of the Datepart function.

DATEPART (Datepart, Date)

Date: Please specify the valid date as the second argument. It can be a column, 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.

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 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)

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]