SQL DATEDIFF 2

SQL DATEDIFF

SQL DATEDIFF Function will find or Calculate the difference between Start Date and End Date. It 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 syntax of this SQL Datediff function is

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. The list of available datepart argument in SQL Server

DatepartAbbreviationsDescription
yearyy,yyyyDisplay the difference between Start and End date in terms of Years
quarterqq, qThis Datepart in Sql Server Datediff display the difference between Start and End date in terms of Quarters
monthmm, mDisplay the difference between Start and End date in terms of Months
dayofyeardy, yDisplay the difference between Start and End date in terms of Days of a Year (1 to 365)
daydd, dThis Datepart in Sql Server Datediff display the difference between Start and End date in terms of Day (1 to 31)
weekwk, wwDisplay the difference between Start and End in terms of Week
weekdaydw, wDisplay the difference between Start and End in terms of Week day
hourhhThis Datepart in Sql Datediff display the difference between Start and End in terms of Hours
minutemi, nDisplay the difference between Startdate and Enddate in terms of Minute
secondss, sDisplay the difference between Start and End in terms of Seconds
millisecondmsDisplay the difference between Start date and End in terms of Milliseconds
microsecondmcsThis Datepart in Sql Server Datediff display the difference between Start and End date in terms of Microsecond
nanosecondnsIt will print the difference between Start and End 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

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

Within the first statement (year, @Date1, @Date2), the SQL Datediff function prints the total number of years between @Date1 variable and @Date2

Next, we asked the Datediff function to display the total number of months between @Date1 variable and @Date2 variable.

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

SELECT 'YEAR' AS [SQLDATEDIFF], 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)
SQL DATEDIFF 1

SQL DATEDIFF Function Example 2

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

The first Datediff sql statement in the 5th line will find the difference between HireDate Column and today in terms of Year.

The next line Date Function will find the difference between HireDate Column and today in terms of Quarters. Similarly, you can select datediff milliseconds, hour, minute, number of days, and days hour.

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 [Employee]

SQL DATEDIFF 2