1

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

Mahmoud Abdelsattar
  • 1,299
  • 1
  • 15
  • 31
Olendris63
  • 97
  • 6

2 Answers2

1

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'
dougp
  • 2,810
  • 1
  • 8
  • 31
  • thank you! the formula breaks when adding the text portion - ... + ' years, ' but it worked for what i was looking for :) – Olendris63 Jun 29 '22 at 16:25
  • You didn't state which version of Cognos or which RDBMS you are using. My code works in Cognos 11.1.7IF9 using SQL Server 2016 – dougp Jun 29 '22 at 18:58
0

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

C'est Moi
  • 326
  • 1
  • 2
  • 8