神刀安全网

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:

SQL Server T-SQL DateDiff Example

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

SQL Server T-SQL DateDiff Example

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]

SQL Server T-SQL DateDiff Example

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.

转载本站任何文章请注明:转载至神刀安全网,谢谢神刀安全网 » SQL Server T-SQL DateDiff Example

分享到:更多 ()

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址