Hey all,

So, I've had a procedure I've been using for an external procedure/population request to populate a form drop-down control. It's been working fine up till recently when the request throws an error due to the DATEADD() function I've been using adding to the day component incorrectly. Today's date is 20150728 (&YYMD) and if I try and add 7 days to today's date I get 20150735 instead of 20150804 for &THISWEEK1. Am I doing something wrong? Is &YYMD not considered a valid full-component date? I try using &DATEYYMD and get something that doesn't even resemble a date --> 41854.

My code:

SET HOLDLIST = PRINTONLY

-SET &THISWEEK1 = DATEADD(&YYMD, 'D', 7);
-*-SET &THISWEEK = EDIT(&THISWEEK1,'9999/99/99');
-SET &YEAR = EDIT(&YYMD,'9999$$$$');

DEFINE FILE DIMDATE
WKEND_MDYY/MDYY = DATECVT(WEEKENDING, 'YYMD', 'MDYY');
END
TABLE FILE DIMDATE
BY HIGHEST WEEKENDING
BY WKEND_MDYY
WHERE WEEKENDING LE '&THISWEEK1';
WHERE FISCALYEAR GE '&YEAR';
ON TABLE PCHOLD FORMAT XML
END
-RUN

Output if I use &YYMD for today's date:

0 NUMBER OF RECORDS IN TABLE=        1  LINES=      1
 0 ERROR AT OR NEAR LINE     18  IN PROCEDURE ADHOCRQ FOCEXEC *
 (FOC177) INVALID DATE CONSTANT: 20150735
 (FOC009) INCOMPLETE REQUEST STATEMENT
 BYPASSING TO END OF COMMAND

Output if I use &DATEYYMD for today's date:

0 NUMBER OF RECORDS IN TABLE=        1  LINES=      1
 0 ERROR AT OR NEAR LINE     18  IN PROCEDURE ADHOCRQ FOCEXEC *
 (FOC177) INVALID DATE CONSTANT: 41854
 (FOC009) INCOMPLETE REQUEST STATEMENT
 BYPASSING TO END OF COMMAND

Any help would be greatly appreciated.

Thanks in advance!

This message has been edited. Last edited by: CoolGuy,

Look it up.

"DATEADD requires a date to be in date format. Since Dialogue Manager interprets a date as alphanumeric or numeric, and DATEADD requires a standard date stored as an offset from the base date, do not use DATEADD with Dialogue Manager unless you first convert the variable used as the input date to an offset from the base date. "

You cannot use the Date functions in Dialogue Manager without first converting the date string into a date value. Check this out:

-SET &DATEIN = '20150328';
-SET &DATEADD6 = DATECVT((DATEADD((DATECVT(&DATEIN, 'I8YYMD', 'YYMD')), 'D', 6)), 'YYMD', 'I8YYMD');
-TYPE IN: &DATEIN / OUT: &DATEADD6

The innermost DATECVT function call converts the string value &DATEIN to a smart date. Then the DATEADD function runs and then the outermost DATECVT function converts the smart date back to a string.

quote:

You cannot use the Date functions in Dialogue Manager without first converting the date string into a date value. Check this out:

-SET &DATEIN = '20150328';
-SET &DATEADD6 = DATECVT((DATEADD((DATECVT(&DATEIN, 'I8YYMD', 'YYMD')), 'D', 6)), 'YYMD', 'I8YYMD');
-TYPE IN: &DATEIN / OUT: &DATEADD6

The innermost DATECVT function call converts the string value &DATEIN to a smart date. Then the DATEADD function runs and then the outermost DATECVT function converts the smart date back to a string.

Francis

Superb answer. This is what keeps the forums so useful, more useful than the manuals or the help files as far as I'm concerned.

make certain that the date fields you want to manipulate in the date functions are in the format the functions expect them to be in. That seems to be the issue.

John, I haven't found any info on using Date functions in the manual. The forum is definitely more helpful. Now, is it "Dialogue Manager" or "Dialog Manager"? That will affect a forum search

Francis,

Thank you so much for explaining the issue so that I could actually understand what I had skipped a beat on, and then giving me an example of how it should be done! If only all responses were similar to yours. I really appreciate the help. If I could buy ya lunch, I would. lol I wasn't sure if &YYMD was indeed treated as a string or a date. The manual really doesn't make it too clear (at least for me). So, first you must convert it to a date, add whatever date component, then convert it back to a string for use. Gotchya.

Again, thank you for being patient and kind enough to help me learn the ropes. Somewhat new to all this WF and DM voodoo.

And Jack, I always try to glean from the manual before I come to the forums or open a case. A lot of the time either the manual is lacking, or I just don't understand what it is trying to get at. Thanks though for your input.

Thanks to all who tried to help!

I have an old paper copy of the "(Almost) 1001 Ways to Work With Dates in WebFOCUS" book that was published about ten years back. Very useful, but it's strange looking in an index for something after years of typing words into search boxes.

It used to be $40, now it's $117. $97 in ebook form. http://www.aviter.com/keysheet/home/dates-book/

If you use dates a lot it may be worth dropping a C-note on this critter. Or just ask Francis.

Old version is still available for $40 here -- http://documentation.informati...400251.1106&Price=40

That's a paper copy. 360 pages of dateness.

Thanks for the links and info John!