Work around for calculating age using the SQL Server DateDiff function

By:   |   Comments (35)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | > Dates


Problem

Have you ever wanted to compute age, but the results from the DATEDIFF function seemed to be wrong some of the time? In this tip we cover why the DATEDIFF function does not always reliably compute age?

Solution

The SQL Server online documentation describes how to compute the number of datepart boundary crossings between two dates and/or times with the DATEDIFF function. The documentation indicates you can specify many different types of boundary crossings, such as borders betweens years, quarters, months, days of a year, a month, or a week. If you do not think about this much, you might conclude that boundary crossings for years correctly reflects the duration in years between two dates or datetime values. This would be a mistake.

This tip explains why the number of year boundary crossings does not reliably compute the duration between two dates or datetime values. The tip also applies this understanding to reliably computing the age in years between two date values, such as a birthdate and a hospital admission date .

Demonstrating the Age In Years Issue for the DATEDIFF Function

When you specify a DATEDIFF function, you indicate two date or datetime values as well as the datepart for which you want to count the boundary crossings. The first date is often called the start date and the second date the end date, but there is no requirement for the values to be in a specific order. In the source data for a query or the parameter values for a stored procedure, the "start date" can always precede, always follow, or sometimes precede and sometimes follow the "end date". For example, while it is normal with new born babies for the birth date to be after the admission date for entry to a hospital, it may be necessary sometimes for a baby to return to the hospital shortly after birth for a medical procedure. On the return visit, the birth date will always be before the hospital admission date

The screen shot below illustrates two cases in which the start date is before the end date. The age in days between the two dates is either 2 or 3 days, but in one case the DATEDIFF function returns an Int data type. AgeInYears value of 0 while in the other case the AgeInYears value is 1.

a DATEDIFF function

It is important to understand that the DATEDIFF function is both reliable and valid in both cases. The DATEDIFF function counts the boundary crossings between the start and end dates identically in both instances. In the first case, both dates are on the same side of the boundary between 2011 and 2012 -- namely, the 2012 side. Because there is no boundary separating these two dates, the Int. AgeInYears value is 0. In the second case, the start date is on the 2011 side of the boundary, and the end date is on the 2012 side of the boundary. There is one year boundary between the two dates, and the DATEDIFF function returns a value of 1 for Int. AgeInYears.

A Fix for the Age In Years Issue with the DATEDIFF Function

A fix for the DATEDIFF function depends on your computing objective. The DATEDIFF function performs correctly, but its application may not be suitable for your computing objective. Let say, your objective is to fix the example above so the Int. AgeInYears value is 0 in both cases -- using the exact same pair of start and end dates specified above.

Looking at the preceding screen shot, you can see that the Int. AgeInYears value is wrong when the day of the year for the start date is greater than the day of the year for the end date. Notice that January 1, 2012 is the first day of the year for 2012, which is obviously before January 3, 2012 in the year 2012. On the other hand, December 31, 2011 is the 365th day of the year for 2011. Therefore, December 31, 2011 has a day of the year value far exceeding the day of the year value for January 3, 2012.

You can use the DATEPART function to assess separately the day of the year for the start date and the end date. In the preceding example, so long as the day of the year value for the start date is less than or equal to the day of the year value for the end date, the count of the year boundary crossings matches the age in years. In contrast, when the day of the year for the start date is greater than the day of the year for the end date, the DATEDIFF function returns an age in years that is one greater than is valid. Therefore, you can fix the problem by reducing the end date by one year in the preceding example. The following screen shot shows how to implement this fix with T-SQL.

implementing this fix with T-SQL.

If you run the preceding script with the same two pair of date values as previously, you'll get the right result for the two different start dates on either side of 2011 year boundary. The screen shot below shows the outcome of running the fixed code. Notice that the Int. AgeInYears value is 0 for both start dates.

the outcome of running the fixed code

A More Robust Fix for the DATEDIFF Function Age In Years Issue

While the above solution fixes the DATEDIFF function age in years issue for the same dates as used in the first example, the solution does not necessarily extend to other pairs of start and end dates. In particular, when the start date is after the end date in the same year, the Int. AgeInYears value equals -1. Also, the AgeInDays value is negative. Since age cannot be negative, we need another, more elaborate fix.

The negative value outcome requires different fixes for years and days. In the case of years, the age in years computes to a negative value of -1 just when the start date is after the end date in the same year. As a result, our fix can check for a -1 computed value and replace it with 0. In the case of days, the age in days computes to a negative value that reflects the true difference in days between the dates. Therefore, for days, we can simply wrap the DATEDIFF function inside an ABS function. The following screen shot shows the application of these ideas to the prior fix.

wrap the DATEDIFF function inside an ABS function

The following excerpt from a Results pane confirms the ability of fixed code to properly handle the negative outcomes from the previous fix. The first row is from the previous fix, and the second row shows the valid results returned by the second fix. Since both the start date and the end date are in the same year, the age in years should obviously be 0 and not -1. In the case of age in days, the start date and end date are one day apart. Age is not directional. It makes no sense to say a baby is - 1 day old. Wrapping the DATEDIFF function in the ABS allows the result to be positive whether the start date is before or after the end date.

Wrapping the DATEDIFF function in the ABS allows the result to be positive whether the start date is before or after the end date

Up until this point, all the examples were for cases in which the age in years should be 0, but the final expression for age in years also works correctly for dates that are one or more years apart. The final expression even validly accounts for leap years. The first row in the following Results pane excerpt shows two dates with an age in days of 1460 days, which happens to be equivalent to 4 times 365. However, the reported age in years between the dates in the first row is 3 years -- not 4 years. This is because the two dates encompass the extra day of February 29, 2012. The inclusion of a leap year can require that the dates be at least 1461 days apart for the difference in years to be 4 years apart. The second row in the excerpt below illustrates that the expression for age in years correctly handles this detail.

The inclusion of a leap year can require that the dates be at least 1461 days apart
Next Steps

This tip is to clarify that the DATEDIFF function counts datepart boundary crossings and not age in a datepart, such as years or days. Given a firm grasp of this issue, you can engineer a solution to compute in age for any datepart that you require. To help empower your customized engineering solutions for the DATEDIFF function, the tip presents a couple of datepart age solutions and illustrates how they functioned in different contexts.

I encourage you to dig deeper to get a firm grasp of exactly how the DATEDIFF function works and how others have addressed the datepart boundary crossing versus datepart age previously.



sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Rick Dobson Rick Dobson is an author and an individual trader. He is also a SQL Server professional with decades of T-SQL experience that includes authoring books, running a national seminar practice, working for businesses on finance and healthcare development projects, and serving as a regular contributor to MSSQLTips.com. He has been growing his Python skills for more than the past half decade -- especially for data visualization and ETL tasks with JSON and CSV files. His most recent professional passions include financial time series data and analyses, AI models, and statistics. He believes the proper application of these skills can help traders and investors to make more profitable decisions.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Thursday, November 19, 2020 - 7:12:56 AM - Gavin Broughton Back To Top (87808)
--Set parameters, or choose from table.column instead:

DECLARE @DOB DATE = '2000/02/29' -- If @DOB is a leap day...
,@ToDate DATE = '2030/03/01' --their 30th birthday in 2030 would be 1st March in this simply calculation

SELECT [Age] = (0+ FORMAT(@ToDate,'yyyyMMdd') - FORMAT(@DOB,'yyyyMMdd') ) /10000
--Note 0+ part just tells SQL to calculate the char(8) as numbers:

Friday, May 8, 2020 - 4:19:45 PM - Greg Hendee Back To Top (85605)

Please note that the article code appears to have an issue with Age in Years for at least one Leap Years:

Start Date of 20181201

End Date of 20201130 reports Int AgeInYears as 3 rather than 2.

Monday, April 1, 2019 - 3:09:27 PM - Rick Dobson Back To Top (79442)

Hi,

Here's a page of links to date processing tips available from MSSQLTips.com (https://www.mssqltips.com/sql-server-tip-category/121/dates/). I think that's going to be your best source for learning about date processing in SQL and getting a solution to work just the way you want.

In the time that I had to devote to your problem, this is what I came up with. It is more granular than you were seeking, but it may be a helpful starting point to get to what you want.

select 
 @dob dob
     ,year(getdate()) today_year
,year(@dob) dob_year
,year(getdate()) - year(@dob) [today_year less dob_year]
,month(getdate()) today_month
,month(@dob) dob_month
,month(getdate()) - month(@dob) [today_month less dob_month]
,case
-- over 26 just by years
when (year(getdate()) - year(@dob)) > 26 then 'over 26 years'
-- over 26 years by months and on 26th year
when (year(getdate()) - (year(@dob))) = 26 
and (month(getdate()) - month(@dob)) > 0 then 'over 26 years prior month this year'
-- turns 26 years old on this month
when (year(getdate()) - (year(getdate())-26)) = 26 
and (month(getdate()) - month(@dob)) = 0 then 'turns 26 this month'
-- turns 26 years old in a later month this year
when (select year(getdate()) - (year(getdate())-26)) = 26 
and (month(getdate()) - month(@dob)) < 0 then 'will turn 26 in a later month this year'
-- less than 26 by years
when (year(getdate()) - year(@dob)) < 26 then 'less than 26 years old'
end age_category

Rick Dobson

Monday, April 1, 2019 - 12:46:22 PM - Hoda Rabile Back To Top (79441)

 Hello-

I wanted to know how can I use DateAdd to get age greater than 26? I want to know those who are already 26, turning 26 on the month I'm running the report and those over 26.

DECLARE @BEGDATE_BACK datetime = DateAdd(year,-26,@BEGDATE) -- begdate is the report begin month

DECLARE @ENDDATE_BACK datetime = DateAdd(year,-26,@ENDDATE) -- enddate is the report end month

 (dateofbirth Between @BEGDATE_BACK And @ENDDATE_BACK) -- this will only pull those of 26 years of age. I need to pull age as mentioned above.

Please email me with your suggestion. Thanks.

Wednesday, July 13, 2016 - 8:53:37 AM - Rick Dobson Back To Top (41880)

 Hi Thomas,

I like the point of your reply.  Keep it simple!

The main point of the article was not about the solution (which you correctly point out is complex), but about a problem with the built-in DATEDIFF function when used to compute the difference in years between two dates that are just days apart but on different sides of a year boundary.  The built-in DATEFIFF function can report these dates as 1 year apart although they are just days apart.

The best workaround to this DATEDIFF function "feature" is a matter of personal preference and business requirements.  You can tell from the wide range of replies, including the one from you, that there are many workarounds worthy of consideration.  The most important matter is to have a workaround and not incorrectly report two dates that are just days apart as one year apart.

 

 

Wednesday, July 13, 2016 - 3:43:26 AM - Thomas Franz Back To Top (41878)

I can't understand why you are using so much complex formulars (calculating with days / leap years / seconds ...) and do not simply use a

SELECT DATEDIFF(MONTH, '20111231', '20120103') / 12

Tuesday, January 12, 2016 - 10:03:48 AM - Jason Hurley Back To Top (40405)

Here's my favorite Age calculation:

FLOOR((CAST(GetDate() AS INTEGER) - CAST(n.DOB AS INTEGER)) / 365.25)

Friday, May 8, 2015 - 5:02:37 AM - Stefan Boumans Back To Top (37133)

If  you want to calculcate the correct Age in any circumstances, use a numeric comparison of the days in YYYYMMDD format, which you could further correct by subtracting the time parts. Calculations including any conditional checking are usually only correct when the second date is on or beyond the first. In my opinion, if you put the dates in reverse order, the returned age value should be the same, only the negative. Also calculations including DayOfYear values, may go wrong with Leap Years.

I defnitely think SQL Server should natively support calculating the correct Age.

 

Wednesday, March 12, 2014 - 8:11:25 PM - Rick Willemain Back To Top (29733)

Original article was presently beautifully. Thank you !

I also appreciated see variations on the same topic.  just wonderful. Thanks to everyone

Wednesday, February 5, 2014 - 11:57:35 AM - Daryl McDaniel Back To Top (29344)

I found this much shorter example somewhere online recently.

Thought it should be shared:

DECLARE
 @DOB DATETIME = '1987-12-13 00:00:00.000'


SELECT
 FLOOR((CONVERT(INT, CONVERT(VARCHAR(8), GETDATE(), 112))
  - CONVERT(INT, CONVERT(VARCHAR(8), @DOB, 112))) / 10000)


It subtracts the two dates in the form of YYYYMMDD, then divide by 10000 leaving only age in whole years.

 

Thursday, July 18, 2013 - 8:50:31 PM - Rick Back To Top (25906)
This works for me - the best of everything I could find. I have not used seconds but you get the idea. This will give -6 days if that's applicable to your dates: Declare @StartDate datetime, @EndDate datetime, @Result varchar(50) SET @StartDate = (Select '2013-07-19 12:20:07.097') Set @EndDate = (SELECT '2013-07-19 15:02:38.000') SET @RESULT = (SELECT 'Starts in: ' + STR(FLOOR(CAST(@newDate-@RunDate AS FLOAT)),1) + ' days ' + convert(varchar,datepart(hour,@EndDate-@StartDate)) + ' hrs ' + convert(varchar,datepart(minute,@EndDate-@StartDate)) + ' mins') Result is: 'Starts in: 0 days 2 hrs 42 mins'

Thursday, May 24, 2012 - 8:29:05 AM - Rick Dobson Back To Top (17634)

Chris Williamson and others who demonstrated their creativity in comments on my article,

Thank you for your very thorough consideration of selected scenarios for computing age with the DATEDIFF function.

After reviewing all the comments, I remain convinced that there are different solutions to the calculation of age with the DATEDIFF function that best serve different business contexts.  I hope readers go beyond seeking the one best T-SQL solution for all contexts to seeking the one that is "best" for their business objective.  What's "best" is a subjective matter that can depend on different criteria -- only one of which is the right answer in all contexts.  For example, if you only need to compute age as less than year or 1 year or more, you may be able to use a simpler solution than one that needs to account for leap years and be exceedingly fast.

I hope Microsoft is paying attention to the wide variety of solutions offered and the relatively large number of comments for this article.  Obviously, people care about the topic.  Do you think it would be helpful if Microsoft gave us a built-in function for calculating age?

Rick Dobson

Thursday, May 24, 2012 - 5:49:12 AM - Chris Williamson Back To Top (17631)

Rick Dobson,

This is a good subject to cover, especially for anyone calculating birthdays or anniversary dates.  However, the calculation is off by 1 year if comparing the same date, after February 28th, with one being a leap year and one not.  The issue is the comparison of Day of Year.

My fix is to introduce the concept of Day of Leap Year.  With Day of Year, the number is inconsistent for dates after 2/28.  For example, 3/01 is 60 in 2011, but 61 in 2012 because 2/29 is 60.  In Day of Leap Year, 60 is reserved for 2/29, even in non-leap years.  You accomplish this by adding 1 to dates over 59 on non-leap years.

Calculating DLY is a bit more code than DY:
DATEPART(DY ,startdate)
becomes:
CASE WHEN DATEPART(DY ,startdate) < 60
         THEN DATEPART(DY ,startdate)
     ELSE DATEPART(DY ,startdate) + CASE WHEN DATEPART(YY ,startdate) % 400 = 0 THEN 0
                                         WHEN DATEPART(YY ,startdate) % 100 = 0 THEN 1
                                         WHEN DATEPART(YY ,startdate) % 4 = 0 THEN 0
                                         ELSE 1
                                     END
END

First, if DY is less than 60, no adjustment needs to be made.  Otherwise, add 1 when the YY is not a leap year.  A leap year is defined as a year divisible by 4 or 400, but not by 100.  This, obviously, could be wrapped into a scalar function.

 

Further ramblings...  Using Day of Leap Year has changed our birthday calculations from a function having to evaluate every row, to using a persisted, calculated field with an Index - much faster.  One caveat: when selecting records with the same Day of Leap Year as today, 'today' may actually have 2 values.  In non-leap years, 2/29 days have to be considered with either 2/28 or 3/01.  That means, either 2/28 would be 59 and 60, or 3/01 would be 60 and 61.  You could avoid this by using Day of Non-Leap Year, meaning subtract 1 from leap years for dates over 59.  The problem now is people born on 2/29 will have their birthdays on 2/28 or 3/01, even during leap years...

 


SQL Proof:

DECLARE @Dates TABLE
    (
     startdate DATE
    ,enddate DATE
    ,note VARCHAR(50) 
    )
--Pre 2/28 tests
INSERT  @Dates VALUES  ('20090101' ,'20100101','<- Correct')
INSERT  @Dates VALUES  ('20090102' ,'20100101','<- Correct')

--Post 2/28 tests, both non-leap years
INSERT  @Dates VALUES  ('20090301' ,'20100301','<- Correct')
INSERT  @Dates VALUES  ('20090302' ,'20100301','<- Correct')

--Post 2/28 tests, both leap years
INSERT  @Dates VALUES  ('20120301' ,'20160301','<- Correct')
INSERT  @Dates VALUES  ('20120302' ,'20160301','<- Correct')

--Post 2/28, one leap year, one non-leap year
INSERT  @Dates VALUES  ('20120301' ,'20130301','<- Incorrect')
INSERT  @Dates VALUES  ('20120302' ,'20130301','<- Correct')


SELECT  startdate
       ,enddate
       ,startedateDY = DATEPART(DY ,startdate)
       ,startdateDLY = CASE WHEN DATEPART(DY ,startdate) < 60 THEN DATEPART(DY ,startdate)
                            ELSE DATEPART(DY ,startdate) + CASE WHEN DATEPART(YY ,startdate) % 400 = 0 THEN 0
                                                                WHEN DATEPART(YY ,startdate) % 100 = 0 THEN 1
                                                                WHEN DATEPART(YY ,startdate) % 4 = 0 THEN 0
                                                                ELSE 1
                                                           END
                       END
       ,enddateDY = DATEPART(DY ,enddate)
       ,enddateDLY = CASE WHEN DATEPART(DY ,enddate) < 60 THEN DATEPART(DY ,enddate)
                          ELSE DATEPART(DY ,enddate) + CASE WHEN DATEPART(YY ,enddate) % 400 = 0 THEN 0
                                                            WHEN DATEPART(YY ,enddate) % 100 = 0 THEN 1
                                                            WHEN DATEPART(YY ,enddate) % 4 = 0 THEN 0
                                                            ELSE 1
                                                       END
                     END
       ,AgeInDays = ABS(DATEDIFF(dd ,startdate ,enddate))
       ,[Int. AgeInYears] = CASE WHEN DATEPART(DY ,startdate) <= DATEPART(DY ,enddate)
                                 THEN DATEDIFF(YY ,startdate ,enddate)
                                 WHEN DATEDIFF(yy ,startdate ,DATEADD(yy ,-1 ,enddate)) = -1 THEN 0
                                 ELSE DATEDIFF(yy ,startdate ,DATEADD(yy ,-1 ,enddate))
                            END
       ,note
       ,[Int. AgeInYears] = CASE WHEN CASE WHEN DATEPART(DY ,startdate) < 60 THEN DATEPART(DY ,startdate)
                                           ELSE DATEPART(DY ,startdate)
                                                + CASE WHEN DATEPART(YY ,startdate) % 400 = 0 THEN 0
                                                       WHEN DATEPART(YY ,startdate) % 100 = 0 THEN 1
                                                       WHEN DATEPART(YY ,startdate) % 4 = 0 THEN 0
                                                       ELSE 1
                                                  END
                                      END <= CASE WHEN DATEPART(DY ,enddate) < 60 THEN DATEPART(DY ,enddate)
                                                  ELSE DATEPART(DY ,enddate)
                                                       + CASE WHEN DATEPART(YY ,enddate) % 400 = 0 THEN 0
                                                              WHEN DATEPART(YY ,enddate) % 100 = 0 THEN 1
                                                              WHEN DATEPART(YY ,enddate) % 4 = 0 THEN 0
                                                              ELSE 1
                                                         END
                                             END THEN DATEDIFF(YY ,startdate ,enddate)
                                 WHEN DATEDIFF(YY ,startdate ,DATEADD(YY ,-1 ,enddate)) = -1 THEN 0
                                 ELSE DATEDIFF(YY ,startdate ,DATEADD(YY ,-1 ,enddate))
                            END
FROM    @Dates

Wednesday, May 23, 2012 - 7:24:57 PM - Peter E. Kierstead Back To Top (17624)
Brent, Sadly your solution won't work directly with DATE data-types. However, you do present the best solution, you just need to CAST the DATE data-types to DATETIME data-types before the subtract operation. Good job!

Wednesday, May 23, 2012 - 3:10:01 PM - Mark Back To Top (17615)

--Years old

declare @d1 datetime, @d2 datetime, @MonthDiff int, @AgeInYears int

Select @MonthDiff=datediff(month,@d1,@d2)

 

select @AgeInYears=

casewhen @MonthDiff%12>0 then @MonthDiff/12 else

       casewhenday(@d2)>day(@d1)then @MonthDiff/12 else @MonthDiff/12 -1 end

end

Wednesday, May 23, 2012 - 12:52:22 PM - Peter E. Kierstead Back To Top (17612)
Sorry for the condensed format... I've never commented on this forum before and it does twisted things with simple space and new-line formatted test!

Wednesday, May 23, 2012 - 12:50:09 PM - Peter E. Kierstead Back To Top (17611)
In my shop most folks simply want to know how old someone is in whole years. Assuming that the start date is less than the end date the T-SQL calculation becomes: Declare @d1 Date,@d2 Date,@y Int,@mmdd1 Int,@mmdd2 Int; Select @d1='1959-04-15',@d2=GetDate(); Select @y=DatePart(Year,@d2)-DatePart(Year,@d1), @mmdd1=Cast(SubString(Convert(VarChar,@d1,112),5,4) as Int), @mmdd2=Cast(SubString(Convert(VarChar,@d2,112),5,4) as Int); Select @y-(Case when @mmdd2<@mmdd1 then 1 else 0 End)[Age in Years];

Wednesday, May 23, 2012 - 8:54:05 AM - Brent Back To Top (17605)

Why not just use SQL's ability to deal with datetime values correctly, and zero the two dates down based on the smaller value? This appears to return the correct value in all cases, without jumping thru all the hoops.

DECLARE @dt1 AS DATEtime

DECLARE @dt2 AS DATEtime

 

DECLARE @yrs1 AS INT, @yrs2 AS INT

 

SET @dt1 = '7/1/07'

SET @dt2 = '5/20/12'

 

SELECT dt1 = @dt1

, dt2 = @dt2

, Years = CASE WHEN @dt1 < @dt2

THEN DATEDIFF(YY, @dt1-@dt1, @dt2-@dt1)

ELSE DATEDIFF(YY, @dt2-@dt2, @dt1-@dt2)

END

 

 

Thursday, May 10, 2012 - 8:22:10 AM - Dave Back To Top (17398)

The version I've been using is the last one attributed to Rob Volk in this forum

http://forum.lessthandot.com/viewtopic.php?f=17&t=554

Dave

 

Wednesday, May 9, 2012 - 10:34:09 PM - Scott Morgan Back To Top (17392)

Hi Rick --- RE: Your question "Can you please share with me the precise two dates which cause my expression to return an invalid result for dates being exactly one year apart?"

I believe you are referring to the following post from Joseph directed to me:

      Wednesday, May 09, 2012 - 12:05:34 PM - Joseph

      Scott Morgan,

      Your queries do not apear to work when the difference is exactly one year,
      i.e. from the a date one year to the same date of the next year.

I responded that maybe the "fudge factors" for such calculations should be:

   (365*303+366*97)/400 = 365.2425 days per year

   (365*303+366*97)/4800 = 30.436875 days per month

Because Leap years occur only 97 times per 400 years.

Not sure if this fixes the problem Joseph was referring to in my formulas . . . I'm not aware of any such problem with your code . . .

As much fun as data math can be, momma says it's time for bed and tomorrow I have to get back to doing some real work :-)

Thanks to all . . . This was a fun exercise.

Wednesday, May 9, 2012 - 2:23:39 PM - Joseph Back To Top (17380)

Sorry for the formatting, hopefully this is a little clearer. You're welcome plus an explination:

SELECT ABS(DATEDIFF(YY, @startdate, @enddate)) - SIGN(SIGN(DATEPART(DY, @enddate) - DATEPART(DY, @startdate)) -1) * SIGN(DATEPART(DY, @enddate) - DATEPART(DY, @startdate))

The breakdown:
Basically the DATEDIFF in years is usually correct, ABS() accounts for startdate before or after the enddate. An issue can arise, as originally noted, when the year boundary is crossed but the DOY boundary has not so the age must be adjusted by -1.
I think a big motivator for people to post alternative approaches to the author’s already functional (except as noted) approach is to one-up on the “elegant”, I know it is for me (good natured, of course ;-).  I have removed the case statement but an argument could be made against my query being more elegant as it’s not as readable or clear as to what is happening. But isn’t all of our elegant code usually this way, say/do the most with the least? The adjustment works this way, if enddate-DOY - startdate-DOY is >= 0 then no adjustment is necessary, otherwise we add -1. SIGN() of the difference gives us 1, 0 or -1, all good except for > 1. If we subtract 1 from this we get 0, -1 and -2, not much help but if we apply SIGN() again we have 0, -1 and -1, almost there. Multiply this by the original SIGN()  and the 0 multipliers always result in a product of 0 and the for the case where the original difference was

Functional? I hope so. Elegant? Maybe. Confusing and difficult to support? Probably. Thanks for the puzzle this morning.

Wednesday, May 9, 2012 - 2:14:15 PM - Rick Dobson Back To Top (17379)

Hi Joseph,

Thanks for the fix.  This topic is so interesting.  There are so many little gotchas and even many more fixes.

Thanks so much for your solution.  As I said, I will still dive deeper into this tonight and report back if I discover anything interesting that you, the great MSSQLTips readers and commenters, have not already noted.

It is great to be a part of such a vibrant community.

Rick Dobson

Wednesday, May 9, 2012 - 1:51:14 PM - Joseph Back To Top (17377)

Rick Dobson,
Also note there could be some issues if your enddate precedes your start date by more than one year.
I believe this query should always give the correct result, for any startdate  [<|=|>] enddate:
SELECT ABS(DATEDIFF(YY, @startdate, @enddate)) - SIGN(SIGN(DATEPART(DY, @enddate) - DATEPART(DY, @startdate)) -1) * SIGN(DATEPART(DY, @enddate) - DATEPART(DY, @startdate))
It is based on your original solution but should take into account the issue I pointed out above

Wednesday, May 9, 2012 - 1:21:39 PM - Rick Dobson Back To Top (17376)

Hi Scott Morgan,

Can you please share with me the precise two dates which cause my expression to return an invalid result for dates being exactly one year apart?  I never tested the case of dates being precisely one year apart, but I did test the case of dates being precisely 3 years apart.  The discussion of that case is the last one in the tip, and it was my impression that the expression worked in that case.  See my commentary on the last example to determine if my interpretation that it works makes sense to you.

In any event, I will look up this evening the code that I used and try it out with the dates that failed for you.  If I discover a fix, I will amend my solution and post it in a comment this evening.

Thanks for digging into the tip so closely.  I definitely appreciate your interest.

Rick Dobson

Wednesday, May 9, 2012 - 12:58:22 PM - Joseph Back To Top (17374)

Scott C,
I was originally thinking of a similar approach however this does not work for a situation I pointed out above, first and last day of a leap year. I'm assuming this is because date 0 is not a leap year so when you do a DATEDIFF in years between it and the difference of the the start and end dates (365 in this example) you will get 1 instead of 0

Wednesday, May 9, 2012 - 12:15:24 PM - Scott C Back To Top (17371)

You can eliminate all the special cases by working with the difference between the dates (absolute value if you're not sure which is larger), and using DATEDIFF to compare it to date 0. 

SELECT  Startdate, Enddate, 
        AgeInYears = DATEDIFF(YEAR, 0, ABS(CAST(Enddate AS FLOAT) - CAST(Startdate AS FLOAT))),
        AgeInDays = DATEDIFF(DAY, 0, ABS(CAST(Enddate AS FLOAT) - CAST(Startdate AS FLOAT)))
FROM (
    SELECT  Startdate = CAST('20120101' AS DATETIME), Enddate = CAST('20120103' AS DATETIME)
    UNION ALL SELECT  Startdate = CAST('20120103' AS DATETIME), Enddate = CAST('20120101' AS DATETIME)
    UNION ALL SELECT  Startdate = CAST('20111231' AS DATETIME), Enddate = CAST('20120103' AS DATETIME)
    UNION ALL SELECT  Startdate = CAST('20120103' AS DATETIME), Enddate = CAST('20111231' AS DATETIME)
    UNION ALL SELECT  Startdate = CAST('20070201' AS DATETIME), Enddate = CAST('20070301' AS DATETIME)
    UNION ALL SELECT  Startdate = CAST('20080201' AS DATETIME), Enddate = CAST('20080301' AS DATETIME)
    UNION ALL SELECT  Startdate = CAST('20110101' AS DATETIME), Enddate = CAST('20120103' AS DATETIME)
    UNION ALL SELECT  Startdate = CAST('20120103' AS DATETIME), Enddate = CAST('20110101' AS DATETIME)
    UNION ALL SELECT  Startdate = CAST('20091231' AS DATETIME), Enddate = CAST('20120103' AS DATETIME)
    UNION ALL SELECT  Startdate = CAST('20120103' AS DATETIME), Enddate = CAST('20091231' AS DATETIME)
    UNION ALL SELECT  Startdate = CAST('19800201' AS DATETIME), Enddate = CAST('20070301' AS DATETIME)
    UNION ALL SELECT  Startdate = CAST('20070301' AS DATETIME), Enddate = CAST('19800201' AS DATETIME)
-- These cases show this method correctly accounts for leap years.  The first two 4-year ranges include one
-- leap year, the third includes two leap years, the fourth has one leap year because 2100 is divisible by 100,
-- and the fifth has two leap years because 2000 is divisible by 400.
    UNION ALL SELECT  Startdate = CAST('20060101' AS DATETIME), Enddate = CAST('20100301' AS DATETIME)
    UNION ALL SELECT  Startdate = CAST('20050101' AS DATETIME), Enddate = CAST('20090301' AS DATETIME)
    UNION ALL SELECT  Startdate = CAST('20040101' AS DATETIME), Enddate = CAST('20080301' AS DATETIME)
    UNION ALL SELECT  Startdate = CAST('21000101' AS DATETIME), Enddate = CAST('21040301' AS DATETIME)
    UNION ALL SELECT  Startdate = CAST('20000101' AS DATETIME), Enddate = CAST('20040301' AS DATETIME)
) d

Wednesday, May 9, 2012 - 12:12:27 PM - Scott Morgan Back To Top (17370)

The "boundary crossing" component of DATEDIFF was news to me.  Thanks :-)

And your example of StartDate > EndDate was a a good "What If" scenario.

My final 2 cents . . .

Because Leap years occur only 97 times per 400 years,

maybe the "fudge factors" for such calculations should be:

   (365*303+366*97)/400 = 365.2425 days per year

   (365*303+366*97)/4800 = 30.436875 days per month

Wednesday, May 9, 2012 - 12:05:34 PM - Joseph Back To Top (17369)

Scott Morgan,

Your queries do not apear to work when the difference is exactly one year, i.e. from the a date one year to the same date of the next year.

 

Rick Dobson,

Thank you for this article. I agree that there are many ways to solve a problem and that chosen solutions can differ depending on the specific requirements or business rules that need to be satisfied. I think the important point to take away from this, as you stated, to be aware of what exactly DATEDIFF does.

Wednesday, May 9, 2012 - 11:53:49 AM - Rick Dobson Back To Top (17368)

I notice several interesting comments and exchanges between MSSQLTips readers appeared very shortly after the initial release of the tip.  This is exactly what I hoped would happen.

I did not write my article to push anyone solution. I wrote the tip to highlight an issue -- namely, that the DATEDIFF function counts boundary crossing and not age in dateparts. These two results can be the same or different depending on the date values in the DATEDIFF function.

Given the understanding that DATEDIFF counts boundary crossings and not age, there are different ways of engineering a solution in different contexts.  None of these ways is necessarily better than the other.

The important point is to remember that there is a difference between datepart boundary crossings and datepart age.  If you need age, then you should be prepared to have a little fun making sure your engineered solution gets the results needed for your context.

Wednesday, May 9, 2012 - 11:51:12 AM - Joseph Back To Top (17366)

Bill,

I don't think the issue with the formula in question pertains to calculation of days, but specificallly when calculating years. For example:

 

SELECT (DATEDIFF(SECOND,'2011-01-01','2011-12-31')/(365*24*3600)) years

 

correctly returns 0 but the same query run for 2012 returns 1 because there are 365 days inclusive for leap years.

Wednesday, May 9, 2012 - 11:15:59 AM - Bill Back To Top (17365)

Dave,

I don't understand your question . . . when I run the followiing SELECT, it calculates correctly.  Notice that the 2nd column represents a leap year value -   2012-02-29.

SELECT

(DATEDIFF(SECOND,'2011-12-31','2012-02-28')/(24*60*60)) days1

,

(

DATEDIFF(SECOND,'2011-12-31','2012-02-29')/(24*60*60)) days2

,

(

DATEDIFF(SECOND,'2011-12-31','2012-03-01')/(24*60*60)) days3

 

Results:

days1 days2 days3
59      60      61

 

Notice in the next SELECT that the 2nd column uses an invalid date value - 2011-02-29

SELECT

(DATEDIFF(SECOND,'2010-12-31','2011-02-28')/(24*60*60)) days1

,

(

DATEDIFF(SECOND,'2010-12-31','2011-02-29')/(24*60*60)) days2

,

(

DATEDIFF(SECOND,'2010-12-31','2011-03-01')/(24*60*60)) days3

 

Results:

Msg 242, Level 16, State 3, Line 1

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Wednesday, May 9, 2012 - 11:13:56 AM - Scott Morgan Back To Top (17364)

/* Using DATEDIFF(DAY,StartDate,EndDate) with some "fudge factors" and additional functions */

/* Within the same Year */
SELECT FLOOR((DATEDIFF(DAY,'2012-01-01','2012-01-03')/365.242199)) years;
SELECT FLOOR((DATEDIFF(DAY,'2012-01-01','2012-01-03')/30.4375)) months;
SELECT  FLOOR(DATEDIFF(DAY,'2012-01-01','2012-01-03')) days;

/* Across a Year boundary */
SELECT FLOOR((DATEDIFF(DAY,'2011-12-31','2012-01-03')/365.242199)) years;
SELECT FLOOR((DATEDIFF(DAY,'2011-12-31','2012-01-03')/30.4375)) months;
SELECT  FLOOR(DATEDIFF(DAY,'2011-12-31','2012-01-03')) days;

/*  For StartDate > EndDate FLOOR() will not work */
SELECT FLOOR((DATEDIFF(DAY,'2012-01-07','2012-01-06')/365.242199)) years;
SELECT FLOOR((DATEDIFF(DAY,'2012-01-07','2012-01-06')/30.4375)) months;
SELECT  FLOOR(DATEDIFF(DAY,'2012-01-07','2012-01-06')) days;

/* Use ABS() and CAST(. . . AS int) instead */
SELECT ABS(CAST((DATEDIFF(DAY,'2012-01-07','2012-01-06')/365.242199) AS int)) years;
SELECT ABS(CAST((DATEDIFF(DAY,'2012-01-07','2012-01-06')/30.4375) AS int)) months;
SELECT  ABS(CAST(DATEDIFF(DAY,'2012-01-07','2012-01-06') AS int)) days;

/* Multiple Year boundaries */
SELECT FLOOR((DATEDIFF(DAY,'2008-01-07','2012-01-06')/365.242199)) years;
SELECT FLOOR((DATEDIFF(DAY,'2008-01-07','2012-01-06')/30.4375)) months;
SELECT  FLOOR(DATEDIFF(DAY,'2008-01-07','2012-01-06')) days;

/* Leap Year within the range */
SELECT FLOOR((DATEDIFF(DAY,'2008-01-06','2012-01-06')/365.242199)) years;
SELECT FLOOR((DATEDIFF(DAY,'2008-01-06','2012-01-06')/30.4375)) months;
SELECT  FLOOR(DATEDIFF(DAY,'2008-01-06','2012-01-06')) days;

 

Wednesday, May 9, 2012 - 10:44:09 AM - Dave Back To Top (17362)

How does

(DATEDIFF(SECOND,'2011-12-31','2012-01-03')/(365*24*3600)) years,
(DATEDIFF(SECOND,'2011-12-31','2012-01-03')/(24*60*60)) dayys

account for leap years? 

Dave

 

 

Wednesday, May 9, 2012 - 9:22:05 AM - jeevan Back To Top (17357)

Hello Rick,

You can as well achieve them using the below logic

(DATEDIFF(SECOND,'2011-12-31','2012-01-03')/(365*24*3600)) years,
(DATEDIFF(SECOND,'2011-12-31','2012-01-03')/(24*60*60)) dayys

What do you reckon?

Regards,

Jeevan Anaparthy

Sr Software Developer

CSC, India

Wednesday, May 9, 2012 - 9:21:17 AM - Ranga Back To Top (17356)

Good tip. Interesting observations. Thanks for sharing.

Also, it will be great if we can copy the sql code in the tip OR the sql script can also be attached as a .sql file to the tip which can be downloaded.

Thanks.

 














get free sql tips
agree to terms