I've got a fairly standard date calculation set up in a master file, since I use it in a few different places. It's defined as such:
DATEDIF(HDATE( field_name,'YYMD'),&YYMD,'D')

It works excellently as defined in the master file.

However, if the exact same formula is used as a compute or define in a fex, the fex-used code comes up with a bad result. Example, 20 million (or so) instead of 6000 (or so). I can't seem to find any correlation between the two numbers, or any reason why the same calculation should work differently. Has anyone got any experience with something like this?

This message has been edited. Last edited by: Keith MacDonald,

Is it possible that the calculated number of days is an aggregated value and that's why you're getting 20 million instead of 6000? I.E. there are multiple input rows with the same date...

If there are joins, is the same column name in another table in the joined structure?

Look at the Help page (in Dev Studio) for DATEDIF, including the examples provided. If either date argument of DATEDDIF() is a constant, it must be put in quotes.

If you omit the quotes, and code
DIFF/I9=DATEDIF( HDATE(my_datetime_field,'YYMD'), &YYMD,'D');
in a fex, the line placed in focstack and executed (today) is
DIFF/I9=DATEDIF( HDATE(my_datetime_field,'YYMD'), 20110713,'D');

Since DEFINE knows that a ("smart") date field is expected, and such fields internally hold integer offsets from a base date around 1900, 20110713 is interpreted as the date 20,110,713 days beyond the base date. Need I say more?

With quotes:
DIFF/I9=DATEDIF( HDATE(my_datetime_field,'YYMD'), '&YYMD','D');
-- the line resolves to
DIFF/I9=DATEDIF( HDATE(my_datetime_field,'YYMD'), '20110713','D');
The quoted numeric string '20110713' is understood, in this context. as representing the date 2011-07-13, and the offset of that date is used.

For a define in the mfd, different logic takes hold (the &YYMD there never gets subject to the substitution & stacking process of lines in a fex). &YYMD gets replaced by a proper date variable initialized to the current date, and all is well. But in a fex you must supply the quotes.

This message has been edited. Last edited by: j.gross,

Beautiful, clear, explanation. Nicely done.

quote:

Originally posted by George Patton:
Beautiful, clear, explanation. Nicely done.

+1 Great explanation Jack.

Agreed very well put. Thank you Jack for the explanation!