Please welcome to the second part of “Age calculation” article. In the first part, I described an algorithm of correct multi-datepart age calculation and presented the T-SQL script implementing that algorithm. In this second part I design a function implementing this Age Calculation algorithm.

To become “functional” the script needs two more features:

• ability to handle “reverse” age: when “Today” is earlier then “Birth Date” (like for BC historical references);
• accept a parameter (or parameters) specifying which date parts to be counted.

To fulfil the first requirement I change the inner most derived table (T1) from the original script:

Now [DateFrom] will always be less or equal to [DateTo], and the [Direction] indicates the case of “reverse age”.

The second requirement may be implemented in several ways. One is a set of parameters defining which date parts to count:

I could also choose a bitwise binary string with each bit of it is functionally equivalent to one of the variables listed above.

Or I could use a literal string made up from single-letter codes representing date parts:

• Y – hours
• M – Months
• D – Days
• H – Hours
• N – Minutes
• S – Seconds

For the purpose of this article I chose the last option, as it gives better presentation and readability:

The parameter, @Units, may contain any number of datepart codes in any order.

Now, we start from calculating the number of whole years:

This returns following result:

This gives us pretty much the same outcome as in part 1 of the article. We've got 4 full years, and the new “DateFrom” represents the date when age of 4 years is reached.

Now let's check what happens if we remove the code “Y” from the parameter @Units:

This is what we get back:

All good, we've got 0 years, and dateFrom remains where it was – on @BirthDate.

Next, Months. The calculations of the required “Tally” records become a bit more complicated. If we did not count years on the previous stage, then we need total number of months between DateFrom and DateTo, otherwise – we need only 12 tally records. Now, we repeat the same logic as in Years calculations:

Now add another layer and get days:

For the same set of parameters we get following outcome:

The last piece of our problem is parsing the time portion into separate time parts, depending on presence of relevant part codes in the parameter @Units.

Good thing – it's all linear from here, so we do not need to use “tally loop” anymore.

Another one we can use to our advantage, the TimePortion returned by the last query contains all non-counted units from the date level. If days/months/years have been omitted then we need simply to multiply the number of days in TimePortion by 24 and add the resulting number to Hours.

The minutes calculation follows the same logic, but it has another another layer for hours. If hours have not been counted (“H” code is not mentioned in @Units), then we need to account for number of minutes within those hours. The layering is similar to what I did for Months after Years, but this time I'm layering not derived tables but CASE statements. The in-line comments should explain the logic:

Another story to this to build seconds. The same logic is used, so no more explanation should be required:

OK, it seem like all the pieces are in place. If anybody feels like adding milliseconds into the calculation, you know what to do.

The last thing not to forget is direction. We need to multiply all the calculated age parts by [Direction] and the function is ready. See the full script attached. This script also contains an example of test script to verify different scenarious.

Conclusion

The article displays a method of age calculation which alows to avoid “day skipping” in age growth sequence. The method allows represent age in variable sets of date-time units. In the last part the method was implemented into a T-SQL in-line table function.

Apart from the direct application obvious from it name, the method may have several other ones. For example, by calculating age in “Years-Days” we can convert calendar dates to Julian Dates (YYYYDDD) and another way around:

declare @BaseDate datetime
set @BaseDate = '20010101'
select DATEADD(d, N, @BaseDate) TheDate, 
    STR(DATEPART(YY, @Basedate) + ac.Years, 4,0) + replace(str(ac.Days + 1, 3,0), ' ', '0') JulianDate
 from dbo.TallyGenerator(0, 10000, DEFAULT, 1)
 cross apply dbo.AgeCalculation (@BaseDate, DATEADD(d, N, @BaseDate), 'YD') ac
 order by N

Good luck with exploring new horizons! I hope you enjoyed the reading.