Number of Days between Today and a Date Field

[LeonelSanchezJr]

I am using the following code to try and get the number of days between an allocated date and today, but I am getting HUGE negative numbers.....
DEF_AGE/I10=DATEDIF(ALLOCATED_DT, &DATEMDYY, 'D');

I am using WebFOCUS Dev Studio 714.

Thanks,

Leo ;-)

 

[jimster06]

Leo-
Are the formats for ALLOCATED_DT and &DATEMDYY the same?

 

[jimster06]

&DATEMDYY brings back a date with separators eg mm/dd/yyyy.

DATEDIF is expecting date in format YYMD (or some variation there of), ths so-called standard date or what we once called a 'smart date' so first make sure that both of your date parms are in standard format.

 

[LeonelSanchezJr]

The ALLOCATED_DT appears as MM/DD/YYYY when I view the DB2 table via Microsoft Access. I have tried several variations including CHGDAT, etc to get them both lined up in the same format.

Strangely enough, when I use the DATEDIFF between 2 fields in the same table, it works perfect.....

Thanks,

Leo ;-)

 

[focwizard]

As Jimster06 mentioned, DATEDIF expects the arguments to be in 'smart date' format. Internally, they are stored as a number, representing the offset from the 'base date'. When you use &DATEMDYY, it sees something like:

07/18/2007

And thinks that's a calculation (17 divided by 18 divided by 2007), which gives an offset of 0, so your second arg is acting 'as if' it was the base date for smart date offsets.

To do this correctly, convert &DATEMDYY to a smart date, like this:

TODAY/MDYY = '&DATEMDYY';

then, replace the &DATEMDYY in the call to DATEDIF with 'TODAY':

DEF_AGE/I10 = DATEDIF(ALLOCATED_DT,TODAY,'D');

 

[LeonelSanchezJr]

Awesome! Thank you so much


Thanks,

Leo ;-)