DATEDIFF – be aware of the boundary conditions
Posted by decipherinfosys on December 23, 2009
Got a call from one of our clients over the past weekend. One of their critical reports based on which the audit was being done for perishable goods was wrongly calculating one of the columns. Upon looking at the requirements and then the code that they were using, it was clear that they had not taken the boundary conditions of the DATEDIFF() function into account. What we mean by that is that DATEDIFF() function should be used carefully when computing year/month/day/time difference between two dates. In order to do that, you should properly understand what this function provides to you. Here is a classic example to show what we mean:
declare @start_date datetime, @end_date datetime
select @start_date = '2009-12-31 23:59:59'
select @end_date = '2010-01-01 00:00:00'
select datediff(yy, @start_date, @end_date)
Above, we have the starting date as the datetime value just before the year 2009 ends and the end date as the start of the new year. If we use the datediff() function using the year as the argument to get the difference in the year value between the two dates, we will get the output as 1. If that is the intention, then it is good. However, if the intention was to find out the actual difference between two dates and then from it, compute the number of years, number of months, number of days and the time difference, then you need to understand the boundary conditions of the arguments and do simple date arithmetic to arrive at your desired result. In the case of the client, they had perishable goods in the store and their expiration date was being computed wrongly since the date arithmetic was not being done correctly based on the boundary conditions.
Depending upon the requirements and how you want the data to be shown, you can come up with different solutions. One such solution has been given by MVP Itzik in his sqlmag posts on datetime calculations that you can read from here. Another very good explanation is available at sqlteam here.