Finding difference between two dates considering Month and Date

In this post, I am going to explain about problem of calculating duration from the two dates. One of the real scenarios I faced was to calculate the Tenure (End Date - Start Date) and categorized into some grouping as follow:

'<3 months'     DATEDIFF(MM,StartDate,ISNULL(EndDay,GETDATE())) <3

'3-6 months'   DATEDIFF(MM,StartDate,ISNULL(EndDay,GETDATE())) <6

'6-12 months' DATEDIFF(MM,StartDate,ISNULL(EndDay,GETDATE())) <12

'1-2 years'    DATEDIFF(MM,StartDate,ISNULL(EndDay,GETDATE())) <24

'2-3 years'    DATEDIFF(MM,StartDate,ISNULL(EndDay,GETDATE())) <36

'3-5 years'    DATEDIFF(MM,StartDate,ISNULL(EndDay,GETDATE())) <60

'5-10 years'   DATEDIFF(MM,StartDate,ISNULL(EndDay,GETDATE())) <120

'>10 years'    DATEDIFF(MM,StartDate,ISNULL(EndDay,GETDATE())) >120

For example, if you look at the below query, it returns 12 so this is go under the 1-2 years group, but if you look at the dates fatefully, you will see that actual difference is less than year, because starting date is 28th Feb and End Date is 10th Feb, so different is less than a year. Because this query is not consider the date part, it only consider the Month part.

To solve this problem, instead of writing a function, can write a nested CASE statement as below:

CASE

WHEN DATEPART(day, StartDate) > DATEPART(day, EndDate)

THEN

CASE

WHEN DATEDIFF(MM,StartDate,ISNULL(EndDate,GETDATE()))-1 <3 THEN '<3 months'

WHEN DATEDIFF(MM,StartDate,ISNULL(EndDate,GETDATE()))-1  <6  THEN '3-6 months'

WHEN DATEDIFF(MM,StartDate,ISNULL(EndDate,GETDATE()))-1  <12  THEN '6-12 months'

WHEN DATEDIFF(MM,StartDate,ISNULL(EndDate,GETDATE()))-1  <24  THEN '1-2 years'

WHEN DATEDIFF(MM,StartDate,ISNULL(EndDate,GETDATE()))-1  <36  THEN '2-3 years'

WHEN DATEDIFF(MM,StartDate,ISNULL(EndDate,GETDATE()))-1  <60  THEN '3-5 years'

WHEN DATEDIFF(MM,StartDate,ISNULL(EndDate,GETDATE()))-1  <120  THEN '5-10 years'

ELSE  '>10 years'

END

ELSE

CASE

WHEN DATEDIFF(MM,StartDate,ISNULL(EndDate,GETDATE())) <3 THEN '<3 months'

WHEN DATEDIFF(MM,StartDate,ISNULL(EndDate,GETDATE())) <6  THEN '3-6 months'

WHEN DATEDIFF(MM,StartDate,ISNULL(EndDate,GETDATE())) <12  THEN '6-12 months'

WHEN DATEDIFF(MM,StartDate,ISNULL(EndDate,GETDATE())) <24  THEN '1-2 years'

WHEN DATEDIFF(MM,StartDate,ISNULL(EndDate,GETDATE()))<36  THEN '2-3 years'

WHEN DATEDIFF(MM,StartDate,ISNULL(EndDate,GETDATE())) <60  THEN '3-5 years'

WHEN DATEDIFF(MM,StartDate,ISNULL(EndDate,GETDATE()))<120  THEN '5-10 years'

ELSE  '>10 years'

END

END

AS Tenure

Cheers!