SQL DATEPART function

SQL DATEPART is one of the Date function is used to 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 basic syntax of the Sql Server Datepart function is as shown below

DATEPART (Datepart, Date)

Datepart: It is the part of a given date which we are going to display as output. The following table will display 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	This will find and display the day of a year number from the user given date (1 to 365)
day	dd, d	This will find and display the day number from a given date (1 to 31)
week	wk, ww	This Sql Server datepart function will display the Week number from the specified date
weekday	dw, w	This will display the Week days number from the given date (0 as Sunday & 6 as Saturday)
hour	hh	This will display the Hour value present in the given date
minute	mi, n	This Sql Server Datepart display the Minute Value present in a given date
second	ss, s	This will display the Seconds Value present in the given date
millisecond	ms	This will display the Milliseconds Value present in a given date
microsecond	mcs	This will display the microsecond Value present in the given date
nanosecond	ns	This will display the Nanoseconds Value from the given date
TZOffset	tz	This will display the Time Zone Offset Value present in the given date
ISO_WEEK	isowk, isoww	This will display the Iso Week Number from a given date

Date: Please specify the valid date as second argument. It can be column, expression or any variable.

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)

OUTPUT

ANALYSIS

We declared one variable and assigned the date and time to that variable

DECLARE @Date datetime2 = '2015-08-25 14:24:04.1234567'

We asked the SQL Datepart function to display the year value from the given date

SELECT 'YEAR', DATEPART(year, @Date)

We asked the Datepart function to display the Month number from the given date

SELECT 'MONTH', DATEPART(month, @Date)

and so on

DATEPART Function Example 2

In this example, we are going to 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]

OUTPUT

ANALYSIS

Below Datepart statement will display the Year value from the Hire Date Column

DATEPART(year, [HireDate]) AS [YEAR]

Below statement will display the Quarter value from the Hire Date Column

DATEPART(quarter, [HireDate]) AS [YEAR]