Problem with Date conversion

Cognos 10 Business Intelligence Report Studio
1

Hi,

I have a date1 in the format DDMMYYYY and date 2 in the format Mon DD,YYYY 12:00:00 AM

I need a difference between the two dates in years

I am trying to use _years_between function to find the difference

How to convert DDMMYYY to Mon DD,YYYY format

Please help guys .

2

Hi Sandy,

Perhaps this works:

to_number(to_char(date1,‘YYYY’)) - to_number(to_char(date2,‘YYYY’))

Regards,

Nanno

3

[quote=“Nanno Blankestijn, post:2, topic:739”]Hi Sandy,

Perhaps this works:

to_number(to_char(date1,‘YYYY’)) - to_number(to_char(date2,‘YYYY’))

Regards,

Nanno[/quote]

Thanks for ur answer.But the answer u provided doesnt apply for the below case.

It will be 2 years. But it is 1 year 11 months and I need the year value

date1 = feb 1 ,2008

date 2= jan15,2010

4

Hi,

select to_number(to_char(to_date(‘15-01-2010’,‘dd-mm-yyyy’),‘YYYY’)) - to_number(to_char(to_date(‘01-02-2008’,‘dd-mm-yyyy’),‘YYYY’)) years from dual

Gives me 2 years

You can put your dates instead of the bold part.

Nanno