×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Number of Days between Today and a Date Field

Number of Days between Today and a Date Field

Number of Days between Today and a Date Field

(OP)
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 winky smile  

RE: Number of Days between Today and a Date Field

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

RE: Number of Days between Today and a Date Field

&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.  

RE: Number of Days between Today and a Date Field

(OP)
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 winky smile  

RE: Number of Days between Today and a Date Field

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:

CODE

TODAY/MDYY = '&DATEMDYY';

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

CODE

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

RE: Number of Days between Today and a Date Field

(OP)
Awesome! Thank you so much thumbsup

Thanks,

Leo winky smile  

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close