SQL DATEDIFF()

Introduction to SQL Datediff()

In SQL server suppose we have dates in our data and we want to know the difference between those dates then we can use the DATEDIFF function to know the difference between those dates in days, months, or years. So this function returns an integer as output and to understand more about this function lets know it’s syntax first.

Syntax of Datediff() in SQL

DATEDIFF (interval, startdate, enddate)

As we can see in this function there are three arguments and all are mandatory for this function to work and return the integer result:

1. Interval – This is also called datepart and it is provided as a string to this function. This argument can be anything that represents a time interval like a month, week, day, year. We can also specify the quarter of the year.

year, yyyy, yy = Year
SELECT DATEDIFF(year, '2010-12-31 23:59:59.9999999', '2011-01-01 00:00:00.0000000');

quarter, qq, q = Quarter
SELECT DATEDIFF(quarter,'2010-12-31 23:59:59.9999999', '2011-01-01 00:00:00.0000000');

month, mm, m = month
SELECT DATEDIFF(month, '2010-12-31 23:59:59.9999999', '2011-01-01 00:00:00.0000000');

dayofyear = Day of the year
SELECT DATEDIFF(dayofyear,'2010-12-31 23:59:59.9999999', '2011-01-01 00:00:00.0000000');

day, dy, y = Da
SELECT DATEDIFF(day,'2010-12-31 23:59:59.9999999', '2011-01-01 00:00:00.0000000');

week, ww, wk = Week
SELECT DATEDIFF(week,'2010-12-31 23:59:59.9999999', '2011-01-01 00:00:00.0000000');

hour, hh = hour
SELECT DATEDIFF(hour,'2010-12-31 23:59:59.9999999', '2011-01-01 00:00:00.0000000');

minute, mi, n = Minute
SELECT DATEDIFF(minute,'2010-12-31 23:59:59.9999999', '2011-01-01 00:00:00.0000000');

second, ss, s = Second
SELECT DATEDIFF(second,'2010-12-31 23:59:59.9999999', '2011-01-01 00:00:00.0000000');

millisecond, ms = Millisecond
SELECT DATEDIFF(millisecond,'2010-12-31 23:59:59.9999999', '2011-01-01 00:00:00.0000000');

microsecond, mcs = Microsecond
SELECT DATEDIFF(microsecond,'2010-12-31 23:59:59.9999999', '2011-01-01 00:00:00.0000000');

2. startdate, enddate – These are the actual dates to get the difference between. This is a mandatory parameter.

This function works in the SQL server starting from the 2008 version, Azure SQL Data Warehouse, Azure SQL Database, Parallel Data Warehouse.

Return Value

• The return value is an int and is expressed by the datepart or the interval boundary which is the difference between the start and end date.
• If the range of the return value for int is out of[-2,147,483,648 to +2,147,483,647], DATEDIFF function returns an error. The max difference between the start and end date is 24 days, 20 hours, 31 minutes, and 23.647 seconds for the millisecond. The max difference is 68 years, 19 days, 3 hours, 14 minutes, and 7 seconds for the second.
• If the start and end date have a date with different data type then DATEDIFF will set 0 the missing parts of the other date which has lower precision
• The above queries have the same start and end values. These are adjacent dates and the difference between them is a hundred nanoseconds (.0000001 second). The start and end dates cross one calendar and the result of each query is 1.

Examples

Here are the examples mention below

Example #1 – Calculating Age

select ID,emp_name,emp_dateOfBirth from Employee

We have the above table Employee which consist of the date of birth and from this, we will calculate the age in terms of a year, month and days in 2 steps

Step 1: Creating a function

CREATE FUNCTION fnEmpComputeAge(@EmpDOB DATETIME)
RETURNS NVARCHAR(50)
AS
BEGIN
DECLARE @AgeTempdate DATETIME, @AgeYears INT, @AgeMonths INT, @AgeDays INT
SELECT @AgeTempdate= @EmpDOB
SELECT @AgeYears=DATEDIFF(YEAR, @AgeTempdate,GETDATE())-CASE WHEN (MONTH(@EmpDOB)>MONTH(GETDATE()))OR(MONTH(@EmpDOB)=MONTH(GETDATE())AND DAY(@EmpDOB)>DAY(GETDATE()))THEN 1 ELSE 0 END
SELECT @AgeTempdate=DATEADD(YEAR, @AgeYears, @AgeTempdate)
SELECT @AgeMonths=DATEDIFF(MONTH, @AgeTempdate,GETDATE())-CASE WHEN DAY(@EmpDOB)>DAY(GETDATE())THEN 1 ELSE 0 END
SELECT @AgeTempdate=DATEADD(MONTH, @AgeMonths, @AgeTempdate)
SELECT @AgeDays=DATEDIFF(DAY, @AgeTempdate,GETDATE())
DECLARE @EmpAge NVARCHAR(50)
SET @EmpAge=Cast(@AgeYears AS NVARCHAR(4))+' AgeYears '+Cast(@AgeMonths AS NVARCHAR(2))+' AgeMonths '+Cast(@AgeDays AS NVARCHAR(2))+' AgeDays Old'
RETURN @EmpAge
End

In the above example, we have created a SQL Function to calculate the age of the employee from the DOB so the function takes @EmpDOBas a parameter and returns NVARCHAR(50). We will see this in action when we run this function. In step, we have created this function.

Then we have declared @AgeTempdate DATETIME, @AgeYearsINT, @AgeMonthsINT, @AgeDaysINT variables. First, we have set the @AgeTempdateto @EmpDOB. Next statement is crucial in which we use the DATEDIFF function to get the year difference from the dob and current date which is calculated using GETDATE function and then we subtract 1 or 0 depending on whether the dob month is greater than a current month or if the dob month is same as a current month and the dob date is greater than current date then in those cases we add 1 or else we add 0.

Then we add the calculated years in the @AgeTempdate using the DATEADD function.
Similarly, we calculated the month and added in @AgeTempdate, and then it is used to calculate days. Next, we declared @EmpAge and set it to the concatenation of the final output. Since the calculation result is in int we used Cast function to convert it into nvarchar.

Step 2: Using the function in the query

select ID,emp_name,emp_dateOfBirth,dbo.fnEmpComputeAge(emp_dateOfBirth) as EmpAge from Employee

The result is as follows:

As we can see we have used dbo.fnEmpComputeAge function and passed emp_dateOfBirth to calculate EmpAge and the result is as above.

Example #2 – Using scalar functions and subqueries for start and end date

SELECT DATEDIFF(day,
(SELECT MIN([ShipDate])FROM Sales.SalesOrderHeader),
(SELECT MAX([ShipDate])FROM Sales.SalesOrderHeader)) as ShippingDateDiff;

The result is as follows:

In this example, we have calculated the shipping date difference using scalar functions and scalar subqueries for min and max.

Example #3 – Using ranking functions for the start date argument

SELECT FirstName as first_name,LastName as last_name,
DATEDIFF(day,ROW_NUMBER() OVER (ORDER BY
DepartmentName),SYSDATETIME()) AS row_number
FROM dbo.DimEmployee;

The result is as follows:

In this function, we have used ROW_NUMBER() ranking function as the start date argument.

Example #4 – Using an aggregate window function for the start date argument

SELECT FirstName as first_name,LastName as last_name,DepartmentName as department_name,
DATEDIFF(year,MAX(HireDate)
OVER (PARTITION BY DepartmentName),SYSDATETIME()) AS HireInterval
FROM dbo.DimEmployee

Conclusion

Hopefully, now you know what DATEDIFF() is in the SQL server and how it is used to calculate results the difference between date according to datepart.

Recommended Articles

This is a guide to SQL DATEDIFF(). Here we discuss the Examples of SQL DATEDIFF() to understand how it is used to calculate results. You may also have a look at the following articles to learn more –

1. SQL UNION ALL
2. SQL Users
3. MySQL Binlog
4. Array in PostgreSQL