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