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
*/

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
```