Search code examples
sql-servercognoscognos-10cognos-bi

How do I apply case function to a Leap Year that occurs every four years to a date formula in Cognos?

How do I apply case function to a Leap Year that occurs every four years to a date formula?

I have this DATA ITEM 1 entitled INSURANCE TERM query in cognos that has a formula inside.

(12 * _days_between([Policy Expiration Date],[Policy Effective Date]))/365

Below is the property of the expression Policy Expiration Date in case you want to know. enter image description here

The formula is used to calculate the Insurance Term. 12 months multiplied to the number of days between [Policy Expiration Date],[Policy Effective Date] in the prompt below. Divided to 365 days as a regular year. enter image description here

How do I incorporate the case function between a regular year and a leap year? Something like this..

Case when Year([Policy Expiration Date]) in ('2013', '2014', '2015') then DATA ITEM 1 else DATE ITEM 2((12 * _days_between([Policy Expiration Date],[Policy Effective Date]))/366)

But I can't afford to write the in(2008-2032 leap year) because it would be too long. Is there a way to do this workaround?

Solution

  • This is too long for a comment and likely incomplete as an answer.

    Not quite sure I get the logic here. Do you have two date columns, one for begin and another for end?

    Let's assume you want to know the difference of days between January 1st and March 1st. The way you are calculating this is challenging for leap years. But using datediff it handles this with no effort at all.

    declare @StartDate date = '2012-01-01'
    , @EndDate date = '2012-03-01'

select DATEDIFF(day, @StartDate, @EndDate)

select @StartDate = '2015-01-01'
    , @EndDate = '2015-03-01'

select DATEDIFF(day, @StartDate, @EndDate)