SQL Server T-SQL DateDiff Example

How do we find the difference between two dates in SQL Server. Not just the difference in the number of days, but also number of weeks, months.

The answer is by using DateDiff in SQL Server. Datediff is also suitable for getting the time elapsed between the start and end of a process.

Here are some real life examples of how t-sql datediff can be used to get a date difference:

1. Calculating the number of days remaining for a postpaid mobile service before it expires? Here an Automated system can calculate the date difference in sql and send an SMS to a customer informing him/her of the number of days remaining.

2. Particularly suitable in embedded systems to note the time taken for each process while manufacturing a product.

3. In library management automation to keep track of the number of days a book had been issued to a customer.

4. For daily login-logout required in an electronic attendance software system of any company. The tsql datediff function can be used to calculate the exact working hours of every employee.

The signature of this function is as follows:

DATEDIFF( interval, date1, date2 )

where date 1 & date 2 can be the actual date or a part of date like an year.

Note: date1 should be greater than or equal to date2.

Interval here can be any of the following as shown in the following chart:


image1-t-sql datediff interval

Let us see some examples to make it more clear and interesting.

select datediff (yy, '1984', '1997')  -- 13 years difference

select datediff (dd, '1984', '1986') -- 731 days difference

select datediff (mm, '1984', '1986') -- 24 months difference

select datediff (qq, '1984', '1986') -- 8 quarters difference

select datediff (hour,'2016/05/02 11:00', '2016/05/02 14:45' 
-- 3 hours difference

Database Oriented Example

The DateDiff SQL function can also be used in a where clause.

Let us create a sample database for the same in our SQL Server instance.

Use Master
go
create database DateDiff_Demo
go
Use DateDiff_Demo
go
create table Compaints_Details
(ComplaintID Integer Identity,
 CustId Varchar(7),
 Complaint_Description Varchar(100),
 Engineer_ID Varchar(4),
 Date_of_Complaint date,
 Date_of_Resolve date
 )
 Insert into Compaints_Details 
(CustiD, Complaint_Description, Engineer_ID, 
 Date_of_Complaint,Date_of_Resolve)
 Values
 ('C1', 'Modem problem', 'E1', '21-Apr-2016', '24-Apr-2016')

 Insert into Compaints_Details 
(CustiD, Complaint_Description, Engineer_ID, 
  Date_of_Complaint,Date_of_Resolve)
 Values
 ('C2', 'Wire Connection problem', 'E1', '22-Apr-2016', '22-Apr-2016')

 Insert into Compaints_Details 
 (CustiD, Complaint_Description, Engineer_ID, 
  Date_of_Complaint,Date_of_Resolve)
 Values
 ('C3', 'Socket problem', 'E1', '23-Apr-2016', '28-Apr-2016')

 Insert into Compaints_Details 
 (CustiD, Complaint_Description, Engineer_ID, 
  Date_of_Complaint,Date_of_Resolve)
 Values
 ('C5', 'LAN problem', 'E1', '29-Apr-2016', '29-Apr-2016')
 GO
 select* from Compaints_Details
 go

Image2-SampleData to run a sql datediff query

Let us say we want to see how many days were elapsed for every complaint to get resolved.

Here we will use the Tsql DateDiff function.

SELECT  [ComplaintID]
      ,[CustId]
      ,[Complaint_Description]
      ,[Engineer_ID]
      ,[Date_of_Complaint]
      ,[Date_of_Resolve]
      , DateDiff(dd, [Date_of_Complaint]
      , [Date_of_Resolve]) as [Days taken to resolve]
FROM [DateDiff_Demo].[dbo].[Compaints_Details]

Image3-DaysTaken before a complaint was resolved

Conclusion:

And that’s how we can use the SQL Server T-SQL DateDiff function to calculate the day, month, year, and time part differences between two specified dates.