Occasionally, there is a need to calculate the between two dates for several reasons. For example, the age of a person, of an asset, age of an event. So, having recently had to think about how to do this in a DataStage Transformer rather than in SQL, I thought it might be good to document a few approaches that can provide the age.
Calculating Age With the DecimalToDecimal function
This code does it at the year level; however, if you need the decimal digits or other handling, the rounding within the DecimalToDecimal function can be changed accordingly.
Age Calculation using Julian Date
DecimalToDecimal((JulianDayFromDate(<>) – JulianDayFromDate(Lnk_In_Tfm.PROCESSING_DT) )/365.25, ‘trunc_zero’)
Age Calculation using Julian Date with Null Handling
Null handling is recommended to prevent job failure if a date is missing from your source input data. This code uses 1901-01-01 as the null replacement value, but it can be any date your business requirement stipulates.
DecimalToDecimal((JulianDayFromDate( NullToValue(<>, StringToDate(‘1901-01-01’,”%yyyy-%mm-%dd”) ) ) – JulianDayFromDate(Lnk_In_Tfm.PROCESSING_DT)) /365.25, ‘trunc_zero’)
Calculate Age Using DaysSinceFromDate
DecimalToDecimal(DaysSinceFromDate(<>, <>) /365.25 , ‘trunc_zero’)
Calculate Age Using DaysSinceFromDate with Null Handling
Here is a second example of applying null handling to the input data.
DecimalToDecimal(DaysSinceFromDate(<>, NullToValue(<< Input date (e.g.Date of Birth) >>, StringToDate(‘1901-01-01’,”%yyyy-%mm-%dd”) ) ) /365.25 , ‘trunc_zero’)
