SQL DATEPART

SQL DATEPART function will extract or display the specified date part from the existing date. This SQL Server Datepart function always returns an integer value. 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 SQL Datepart function

DATEPART (Datepart, Date)

Date: Please specify the valid date as second argument. It can be column, expression or any variable. It is the part of a given date which we are going to display as output. The list of available SQL Server datepart arguments.

DatepartAbbreviationsDescription
yearyy,yyyyThis will display the year value from the given date
quarterqq, qThis Sql Server Datepart find and display the quarter number
monthmm, mThis will find and display the month number
dayofyeardy, yDay of a year number from the user given date (1 to 365)
daydd, dDay number from a given date (1 to 31)
weekwk, wwThis Sql Server datepart function will return the Week number
weekdaydw, wWeek days number from the given date (0 as Sunday & 6 as Saturday)
hourhhPrints the Hour value.
minutemi, nThe Minute Value
secondss, sSeconds Value
millisecondmsMilliseconds Value
microsecondmcsMicrosecond Value present in it
nanosecondnsPrints the Nanoseconds Value.
TZOffsettzPrints the Time Zone Offset Value.
ISO_WEEKisowk, isowwReturns the ISO Week Number

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 operation.

We declared one variable and assigned the date and time to that SQL Server variable. 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 1

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 [Employee]
SQL DATEPART 2

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]