Using DATEDIFF() in SQL

Let's say that you wanted to know the difference between two dates.  Do you want to know the difference in months, days, years, minutes, or seconds?  SQL has a built-in function that allows you to do this.  The name of this functions is DATEDIFF (or date difference).  The function works as follows:

DATEDIFF ( DatePart, StartDate, EndDate)

DatePart is the part of the date that you want to see the difference on.  For example, to see the difference in years, your DatePart would be yy or yyyy.  Some other examples as below:

• Month = mm or m
• Hour = hh or h
• Minute = n or mi
• Second = s or ss
• Day = d or dd

So, where can a function like this be valuable?  I work in the telecom industry and sometimes we have to calculate how long a customer was on a call using the raw records.  In the records, there is a start time and an end time.  I could use DATEDIFF() to find out how many seconds they were on the call, as follows:

SELECT DATEDIFF(ss, StartTime, EndTime) 

FROM CDRTable

As another example, let's say that you are an online sales company and you want to see how many days it took for a package to get to a customer:

SELECT DATEDIFF(dd, ShipDate, ReceivedDate) 

FROM ShippingTable

Make sure that you have the correct date in the correct columns.  If we reversed our date columns (like below), we would end up with a negative number:

SELECT DATEDIFF(dd, ReceivedDate, ShipDate) 

FROM ShippingTable

Where do you think you could use this in your organization?

To read up more on the DATEDIFF function, go here: DATEDIFF()