SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Performaing a Right and Comprehensive Age Calculation Part 2

By Sergiy Floka,

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.

 

Resources:

AgeCalculations article part2.sql
Total article views: 923 | Views in the last 30 days: 4
 
Related Articles
FORUM

How do I calculate the week number given a date?

How do I calculate the week number given a date?

ARTICLE

Calculating Work Days

How many times have you wished that there was a parameter for DATEPART to allow you to check for wor...

SCRIPT

Calculating the Number of Business Hours Passed Since a Point of Time

A set of user defined functions used to calculate the number of business or working hours (excluding...

ARTICLE

Sending a Variable Number of Elements in a Parameter

What are your options for sending a variable number of choices in a parameter to a stored procedure?...

FORUM

? How to change default checked multivalue parameter from another?

Need to select one parameter and have it change the default selection on another.

Tags
age    
age calculation    
function    
t-sql    
 
Contribute