SQL DATEDIFF Function

SQL DATEDIFF is one of the Date Function is used to find or Calculate the difference between Start Date and End Date. This DATEDIFF function always return signed integer value.

For example, If you want to extract year, month or quarter from the existing Date, you can use this SQL Datediff function.

SQL DATEDIFF Syntax

The basic syntax behind this SQL Datediff function is as shown below

DATEDIFF (Datepart, Start Date, End Date)

Datepart: This is the part of a given date on which we are going to calculate the difference between Start Date and End Date. Following table will display the list of available datepart argument in SQL Server

Datepart	Abbreviations	Description
year	yy,yyyy	Display the difference between Start and End date in terms of Years
quarter	qq, q	This Datepart in Sql Server Datediff display the difference between Start and End date in terms of Quarters
month	mm, m	Display the difference between Start and End date in terms of Months
dayofyear	dy, y	Display the difference between Start and End date in terms of Days of a Year (1 to 365)
day	dd, d	This Datepart in Sql Server Datediff display the difference between Start and End date in terms of Day (1 to 31)
week	wk, ww	Display the difference between Start and End date in terms of Week
weekday	dw, w	Display the difference between Start and End date in terms of Week day
hour	hh	This Datepart in Sql Datediff display the difference between Start and End date in terms of Hours
minute	mi, n	Display the difference between Start and End date in terms of Minute
second	ss, s	Display the difference between Start and End date in terms of Seconds
millisecond	ms	Display the difference between Start and End date in terms of Milliseconds
microsecond	mcs	This Datepart in Sql Server Datediff display the difference between Start and End date in terms of Microsecond
nanosecond	ns	Display the difference between Start and End date in terms of Nanosecond

Start Date: Please specify the valid date as second argument (Starting Date). It can be column, expression or any SQL Server variable.

End Date: Please specify the valid date as third argument (Ending Date). It can be column, expression or any variable.

SQL DATEDIFF Function Example 1

In this SQL Datediff example we are going to declare two variables of datetime2 data type.

Let us assign valid date to both the variables and perform all the available SQL datediff operation

DECLARE @Date1 datetime2 = '2015-01-01 09:12:54.1234567',
        @Date2 datetime2 = '2015-08-25 14:24:04.1234567'

SELECT 'YEAR' AS [SQL DATEDIFF], DATEDIFF(year, @Date1, @Date2) AS [Values] 
UNION ALL
SELECT 'QUARTER', DATEDIFF(quarter, @Date1, @Date2) 
UNION ALL
SELECT 'MONTH', DATEDIFF(month, @Date1, @Date2) 
UNION ALL
SELECT 'DAYOFYEAR', DATEDIFF(dayofyear, @Date1, @Date2) 
UNION ALL
SELECT 'DAY', DATEDIFF(day, @Date1, @Date2) 
UNION ALL
SELECT 'WEEK', DATEDIFF(week, @Date1, @Date2) 
UNION ALL
SELECT 'WEEKDAY', DATEDIFF(weekday, @Date1, @Date2) 
UNION ALL
SELECT 'HOUR', DATEDIFF (hour, @Date1, @Date2)

OUTPUT

ANALYSIS

First, We declared two variable @Date1, @Date2 and assigned the different date and time to that variables

DECLARE @Date1 datetime2 = '2015-01-01 09:12:54.1234567',
        @Date2 datetime2 = '2015-08-25 14:24:04.1234567'

We asked the SQL Datediff function to display the total number of years between @Date1 variable and @Date2

SELECT 'YEAR', DATEDIFF(year, @Date1, @Date2)

We asked the Datediff function to display the total number of months between @Date1 variable and @Date2

SELECT 'MONTH', DATEDIFF(month, @Date1, @Date2)

DATEDIFF Function Example 2

In this example, we are going to use one of the custom table to perform SQL Datediff operations on Hire Date column and built-in sql function GETDATE().

SELECT [FirstName] + ' '+ [LastName] AS [Full Name]
      ,[Occupation]
      ,[YearlyIncome]
      ,[HireDate]
      ,DATEDIFF (year, [HireDate], GETDATE()) AS [YEARS]
      ,DATEDIFF (quarter, [HireDate], GETDATE()) AS [QUARTERS]
      ,DATEDIFF (month, [HireDate], GETDATE()) AS [MONTHS]
      ,DATEDIFF (day, [HireDate], GETDATE()) AS [DAYS]
  FROM [SQL Tutorial].[dbo].[Employee]

OUTPUT

ANALYSIS

Below Datediff statement will find the difference between Hire Date Column and today in terms of Year

DATEDIFF (year, [HireDate], GETDATE()) AS [YEARS]

The below Date Function will find the difference between Hire Date Column and today in terms of Quarters

DATEDIFF (quarter, [HireDate], GETDATE()) AS [QUARTERS]