InfoSphere DataStage – How to calculate age in a transformer
Age
Occasionally, there is a need to calculate the between two dates for any number of 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 in SQL, I thought it might be good to document a couple of approaches, which can provide the age. This code does it at the year level, however, if you need the decimal digits or other handling them 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
If a date can be missing from you source input data, then null handling is recommended to prevent job failure. This code uses 1901-01-01 as the null replacement values, 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 null handling being applied 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’)