This post is to highlight a fairly useful script I came up with to break down differences between dates. I was originally trying to use DATEDIFF to do my work but there is something a little quirky about DATEDIFF that made my goal tougher to reach. In short, what I was trying to do was to get the "Quantity" of time between two dates. For example, say I have the following two dates:
March 1, 2015 and April 3, 2015
If I were to ask SQL what the difference between these two dates were in Years, I would get a zero.
Print DateDiff(yy, 'March 1, 2015','April 3, 2015')
Results in 0. This is totally expected. If I were to ask for the months in between, then I get the following.
Print DateDiff(mm, 'March 1, 2015','April 3, 2015')
Results in 1. Eureka, he cried, I have found my functions. I can just do a little math in the background and presto, I can build a string that is much more readable. However, during this development, I found the an interesting quirk. Suppose I were to run this.
Print DateDiff(yy, 'December 1, 2014','April 3, 2015')
Results in 1. Wait a friggin second. I know that there is not 1 full year between 1/1/2014 and 4/3/2015. That's only about 4 months. So I did a little digging and found this.
https://msdn.microsoft.com/en-us/library/ms189794.aspx
It basically states the following. "datepart: Is the part of startdate and enddate that specifies the type of boundary crossed...." What the.... So according to TSQL if I ask for the difference between 12/30/2014 at 11:59 PM and 1/1/2015 at 12:01, it is going to tell me 1 Year because we happened to cross a boundary. Correct me if I am wrong, but this is bit misleading. I want the Difference in the date (Hence the name DateDiff). So back to my problem, I want to know how many years, months, days, hourse, minutes, etc... have passed between the two dates. If I can't use DateDiff to get the actual difference in the dates, then how do I do it? What I came up with was to use the DateAdd function. Here is how it works...
Suppose I have two dates and I want to know if there is a year between them. I can Add a year to date one and check to see if has surpassed date 2. If it has (or it is equal), then I know there is at least one year between them. If not, then I know there is less than a year between them. So I can create a User Function that takes two dates, and then using While loops, I can move the original date forward until the "Next" advance would move past the second date. Once I do that, I switch to the next unit of time. I keep going until I have reached the precision I want. I can then format the output any way I like and there you have it, a more readable listing of the time passed between two dates, regardless of boundaries. So without further delay, here is the function I wrote.
ALTER Function [dbo].[udfExactDateDiffInString](
@Date1 datetime,
@Date2 datetime
)
Returns varchar(255)
As
Begin
Declare @return varchar(255)
/*
The nature of this function is to return a descriptive string
around the difference of two dates. We can always do a date
diff to get one measurement of time (days, years, minutes, seconds, etc...)
What this will do is to give us a string that looks like this.
2 Years 3 Months 5 Days
This is accomplished by breaking down the time involved using the
normal date Add functions.
*/
Set @Return = '' -- Avoid conflict with null concat
Declare @Years int
Declare @Months int
Declare @Days int
Declare @Hours int
Declare @minutes int
Declare @Seconds int
-- Intitialize all the values.
Select @Years = 0, @Months = 0, @Days = 0,
@Hours = 0, @Minutes = 0, @Seconds = 0
While DateAdd(yy, 1, @Date1) < @Date2
Select @Years = @Years + 1, @Date1 = DateAdd(yy, 1, @Date1)
While DateAdd(mm, 1, @Date1) < @Date2
Select @Months = @Months + 1, @Date1 = DateAdd(mm, 1, @Date1)
While DateAdd(dd, 1, @Date1) < @Date2
Select @Days = @Days + 1, @Date1 = DateAdd(dd, 1, @Date1)
While DateAdd(hh, 1, @Date1) < @Date2
Select @Hours = @Hours + 1, @Date1 = DateAdd(hh, 1, @Date1)
While DateAdd(mi, 1, @Date1) < @Date2
Select @Minutes = @Minutes + 1, @Date1 = DateAdd(mi, 1, @Date1)
While DateAdd(ss, 1, @Date1) < @Date2
Select @Seconds = @Seconds + 1, @Date1 = DateAdd(ss, 1, @Date1)
-- Now that we have our numbers, let's format them. To make it readable
-- we will need to take into account plural forms.
Set @return = @return + Case
When @Years = 1 Then Convert(varchar,@Years) + ' year '
When @Years > 1 Then Convert(varchar,@Years) + ' years '
When @Years = 0 Then '' End
Set @return = @return + Case
When @Months = 1 Then Convert(varchar,@Months) + ' month '
When @Months > 1 Then Convert(varchar,@Months) + ' months '
When @Months = 0 Then '' End
Set @return = @return + Case
When @Days = 1 Then Convert(varchar,@Days) + ' day '
When @Days > 1 Then Convert(varchar,@Days) + ' days '
When @Days = 0 Then '' End
Set @return = @return + Case
When @Hours = 1 Then Convert(varchar,@Hours) + ' hour '
When @Hours > 1 Then Convert(varchar,@Hours) + ' hours '
When @Hours = 0 Then '' End
Set @return = @return + Case
When @Minutes = 1 Then Convert(varchar,@Minutes) + ' minute '
When @Minutes > 1 Then Convert(varchar,@Minutes) + ' minutes '
When @Minutes = 0 Then '' End
Set @return = @return + Case
When @Seconds = 1 Then Convert(varchar,@Seconds) + ' second '
When @Seconds > 1 Then Convert(varchar,@Seconds) + ' seconds '
When @Seconds = 0 Then '' End
Return @return
End