Difference between two dates in DB2 v8 2

[SantabarbaraKid]

First off I am brand new to DB2. I am actually creating a Universe in Business Objects, but I am posting here because I am getting incorrect results when I run my query on the Database. I have two fields Policy Effective date and Policy Cancel effective date. I want to find the difference in days between these two fields. If the difference between them is <=90 I want to set a 1 if it is greater than 90 I want to set a 0. This is what I have come up with:

SELECT
UV_QUOTE.POLICY_EFFECTIVE_DATE,
UV_QUOTE.POLICY_CANCEL_EFFECTIVE_DATE,
case when day(UV_QUOTE.POLICY_CANCEL_EFFECTIVE_DATE - UV_QUOTE.POLICY_EFFECTIVE_DATE) <= 90 then 1 else 0 End

FROM
(
select * from QHDM.FT_QUOTE_MQT
) AS UV_QUOTE
WITH UR

This does not work. I am getting a 1 for everything that has values for both Policy effective and Policy Cancel. I even tried this Syntax:

case when days(UV_QUOTE.POLICY_CANCEL_EFFECTIVE_DATE) – days(UV_QUOTE.POLICY_EFFECTIVE_DATE) <= 90 then 1 else 0 End

Same exact issue. I have tried Timestamp but my database doesn’t seem to recognize this function. It appears that my query is just extracting the day from each and subtracting them from each other instead of subtracting the two dates from one another. I have done numerous searches, have consulted the db2 SQL cookbook, and according to it my Syntax should work, but it isn’t. Any ideas? Like I said I am very new to DB2 so I could just be making a boneheaded mistake. Any help is greatly appreciated!!!

 

[ddiamond]

As long as your dates are type date, the following should work:

SELECT
  UV_QUOTE.POLICY_EFFECTIVE_DATE,
  UV_QUOTE.POLICY_CANCEL_EFFECTIVE_DATE,
  case when UV_QUOTE.POLICY_CANCEL_EFFECTIVE_DATE - UV_QUOTE.POLICY_EFFECTIVE_DATE  <= 90   then 1 else 0 End

 

[Truusvlugindewind]

http://www.ibm.com/developerworks/d...211yip/0211yip3.html?S_TACT=105AGX11&S_CMP=LP

 

[BrianTyler]

I noticed that in the first example you used DAY(...). This extracts the day portion of the date.

DAYS(..) extracts the relative day number.

TIMESTAMP is used for datetime fields in the format yyyy-mm-dd-hh.mm.ss.nnnnnn

 

[ddiamond]

Truusvlugindewind,

Great link. Have a star.