How to return Dates between in format year-months?

user_19392975 0
Viewed 44

Is there a way to return dates between in format year-months in Cognos reports?

Example: I've been using the following to figure out "age"

  • _years_between (current_date, [DateOfBirth]).

  • Result comes out as just the age in years.

I'm looking for a way to return Age in the following format: preferably

12 years, 6 months

But can work with just a numerical number like the below:

12.5

difference-between-rows yearmonth cognos between
2 Answers

A simple expression should do:

cast(floor (_months_between (current_date, [DateOfBirth]) / 12), int) || ' years, ' || 
mod(_months_between (current_date, [DateOfBirth]), 12) || ' months'

You can even get fancy and omit the "s" in "years" or "months" if the value is 1. I'll leave that part to you.

...and here it is using the _age() function as suggested by C'est Moi.

cast(floor(_age([DateOfBirth]) / 10000), int) || ' years, ' || 
cast(floor(mod(_age([DateOfBirth]), 10000) / 100), int) || ' months'
user_99370260

Why don't you use the _age function?

_age ( date_expression ) Returns a number that is obtained from subtracting "date_expression" from today's date. The returned value has the form YYYYMMDD, where YYYY represents the number of years, MM represents the number of months, and DD represents the number of days.

1985-11-04 360725 This is 36 years, 7 months, and 25 days.

1979-08-12 421017

user_85088110
Related