The user enters a Date Range. I want to build a new table with all dates in this date range as my column. For example,
From Date : 03/20/2010 To Date : 04/31/2010
My Table should look like:
New Date
03/21/2010
03/22/2010
03/23/2010
….
04/31/2010.

This is my code:
-DEFAULT &FROMDT ='FOC_NONE'
-DEFAULT &TODT ='FOC_NONE'

-***TEST
-SET &FROMDT ='03/20/2010';
-SET &TODT ='04/31/2010';
-*****

FROMDATE/MDYY='&FROMDT';
TODATE/MDYY='&TODT';

NOOFDAYS=DATEDIF(FROMDATE,TODATE,'D');

-SET &NEWDATE = FROMDATE;
-REPEAT DATE_LOOP WHILE &NEWDATE LE NOOFDAYS;
-TYPE &NEWDATE
-SET &NEWDATE = &NEWDATE + 1;
-DATE_LOOP TYPE END: &NEWDATE

When I Run this I get an error msg :
“0 ERROR AT OR NEAR LINE 14 IN PROCEDURE BuildinDate.fexFOC
(FOC281) ALPHA ARGUMENTS IN PLACE WHERE NUMERIC ARE CALLED FOR”
I am a still new to webFocus and I need real help. Please could you help me with the right code. Also what is the command I should use to write it into a table. Appreciate all the help.

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

add a line at the start of your code that says

-SET &ECHO=ALL;

that will show more when you run

this couses the problem....it is not the correct code...
-SET &NEWDATE = &NEWDATE + 1;

search in this forum to find the correct formula
should be something like DATEADD

Focus 101....

Dialog manager variables basically have one format, text. they can also be numeric, but that about it.

As you have dates, and want to add days to them the + 1 will just fail because you are adding 1 to a text string (The slashes make it one).

Follow Franks suggestion, you code is almost there.

Here is what I did to twist and do calculation on the date.

  
-***TEST
-SET &FROMDT ='02/20/2000';
-SET &TODT ='03/20/2000';
-*****
-SET &FROMDATE = STRIP(10, &FROMDT, '/', 'A8');
-SET &FROMDATE = DATECVT(&FROMDATE, 'I8MDYY', 'YMD');
-SET &TODATE = STRIP(10, &TODT, '/', 'A8');
-SET &TODATE = DATECVT(&TODATE, 'I8MDYY', 'YMD');
-TYPE (&FROMDATE)  (&TODATE)

-SET &NOOFDAYS=DATEDIF(&FROMDATE,&TODATE,'D');
-TYPE &NOOFDAYS

-SET &NEWDATE = &FROMDATE;
-*REPEAT DATE_LOOP WHILE &NEWDATE LE &TODATE;
-REPEAT DATE_LOOP &NOOFDAYS TIMES;
-TYPE &NEWDATE
-*SET &NEWDATE = &NEWDATE + 1;
-SET &NEWDATE = DATEADD(&NEWDATE, 'D', 1);
-SET &NEWDT = EDIT(DATECVT(&NEWDATE, 'YMD', 'I8MDYY'),'99/99/9999');
-TYPE &NEWDT
-DATE_LOOP 
-SET &NEWDT = EDIT(DATECVT(&NEWDATE, 'YMD', 'I8MDYY'),'99/99/9999');
-TYPE END: &NEWDATE (&NEWDT)

+1 TCL. This is actually exactly the problem I was trying to solve and ran across this post as you wrote it. I love the internet.

- ABT

quote:

FROMDATE/MDYY='&FROMDT';
TODATE/MDYY='&TODT';

NOOFDAYS=DATEDIF(FROMDATE,TODATE,'D');

-SET &NEWDATE = FROMDATE;
-REPEAT DATE_LOOP WHILE &NEWDATE LE NOOFDAYS;

The reason for your error is that you are missing -SET & for FROMDATE , TODATE and NOOFDAYS

and & for the second NOOFDAYS

Once you have this, only part of the question is solved
You have tot WRITE the data to a file, put it in a database or use it in an other way.

Would tis be a once only report, or is everyboday able to run this any time of the day?

How many tables with date sequence files will you have after some time

Thank you everybody...It worked.

I put the data on to a file and it works just fine.

quote:

Originally posted by TCL:
Here is what I did to twist and do calculation on the date.

  
-***TEST
-SET &FROMDT ='02/20/2000';
-SET &TODT ='03/20/2000';
-*****
-SET &FROMDATE = STRIP(10, &FROMDT, '/', 'A8');
-SET &FROMDATE = DATECVT(&FROMDATE, 'I8MDYY', 'YMD');
-SET &TODATE = STRIP(10, &TODT, '/', 'A8');
-SET &TODATE = DATECVT(&TODATE, 'I8MDYY', 'YMD');
-TYPE (&FROMDATE)  (&TODATE)

-SET &NOOFDAYS=DATEDIF(&FROMDATE,&TODATE,'D');
-TYPE &NOOFDAYS

-SET &NEWDATE = &FROMDATE;
-*REPEAT DATE_LOOP WHILE &NEWDATE LE &TODATE;
-REPEAT DATE_LOOP &NOOFDAYS TIMES;
-TYPE &NEWDATE
-*SET &NEWDATE = &NEWDATE + 1;
-SET &NEWDATE = DATEADD(&NEWDATE, 'D', 1);
-SET &NEWDT = EDIT(DATECVT(&NEWDATE, 'YMD', 'I8MDYY'),'99/99/9999');
-TYPE &NEWDT
-DATE_LOOP 
-SET &NEWDT = EDIT(DATECVT(&NEWDATE, 'YMD', 'I8MDYY'),'99/99/9999');
-TYPE END: &NEWDATE (&NEWDT)

Is it possible to increment the date in Dialog Manager as well?
-SET &NEXTDATE = HADD(&MYDATE, 'DAY', 1, 8, 'HYYMDS');

- ABT

In fact, you can increase the date simply using

-SET &NEWDATE = &NEWDATE + 1;

in the original code.

quote:

Originally posted by TCL:
In fact, you can increase the date simply using

-SET &NEWDATE = &NEWDATE + 1;

in the original code.

Sorry TCL, that gives me dates like 4/32 and 2/30. The HADD function would increment to the next realistic date. Your code works well until a user wants to span a month.

-ABT

The code works perfectly well and gives all realistic dates with my environment. I ran the code below:

  
-***TEST
-SET &FROMDT ='02/27/2000';
-SET &TODT ='03/02/2000';
-*****
-SET &FROMDATE = STRIP(10, &FROMDT, '/', 'A8');
-SET &FROMDATE = DATECVT(&FROMDATE, 'I8MDYY', 'YMD');
-SET &TODATE = STRIP(10, &TODT, '/', 'A8');
-SET &TODATE = DATECVT(&TODATE, 'I8MDYY', 'YMD');
-TYPE (&FROMDATE)  (&TODATE)
-SET &NEWDATE = &FROMDATE - 1;
-REPEAT DATE_LOOP WHILE &NEWDATE LT &TODATE;
-SET &NEWDATE = &NEWDATE + 1;
-SET &NEWDT = EDIT(DATECVT(&NEWDATE, 'YMD', 'I8MDYY'),'99/99/9999');
-TYPE &NEWDT
-DATE_LOOP
-TYPE END: &NEWDATE (&NEWDT)

And produced the outcome as following:

-***TEST
-SET &FROMDT ='02/27/2000';
-SET &TODT ='03/02/2000';
-*****
-SET &FROMDATE = STRIP(10, 02/27/2000, '/', 'A8');
-SET &FROMDATE = DATECVT(02272000, 'I8MDYY', 'YMD');
-SET &TODATE = STRIP(10, 03/02/2000, '/', 'A8');
-SET &TODATE = DATECVT(03022000, 'I8MDYY', 'YMD');
-TYPE (36217) (36221)
(36217) (36221)
-SET &NEWDATE = 36217 - 1;
- &DATE_LOOP= 36216 LT 36221;
-SET &NEWDATE = 36216 + 1;
-SET &NEWDT = EDIT(DATECVT(36217, 'YMD', 'I8MDYY'),'99/99/9999');
-TYPE 02/27/2000
02/27/2000
- &DATE_LOOP= 36217 LT 36221;
-SET &NEWDATE = 36217 + 1;
-SET &NEWDT = EDIT(DATECVT(36218, 'YMD', 'I8MDYY'),'99/99/9999');
-TYPE 02/28/2000
02/28/2000
- &DATE_LOOP= 36218 LT 36221;
-SET &NEWDATE = 36218 + 1;
-SET &NEWDT = EDIT(DATECVT(36219, 'YMD', 'I8MDYY'),'99/99/9999');
-TYPE 02/29/2000
02/29/2000
- &DATE_LOOP= 36219 LT 36221;
-SET &NEWDATE = 36219 + 1;
-SET &NEWDT = EDIT(DATECVT(36220, 'YMD', 'I8MDYY'),'99/99/9999');
-TYPE 03/01/2000
03/01/2000
- &DATE_LOOP= 36220 LT 36221;
-SET &NEWDATE = 36220 + 1;
-SET &NEWDT = EDIT(DATECVT(36221, 'YMD', 'I8MDYY'),'99/99/9999');
-TYPE 03/02/2000
03/02/2000
- &DATE_LOOP= 36221 LT 36221;
-DATE_LOOP
-TYPE END: 36221 (03/02/2000)
END: 36221 (03/02/2000)

If you guys read the documentation, you will find:

1. Every & variable in Dialogue Manager is alphanumeric;
2. Many of the date functions (DATEDIF, DATEADD, DATEMOV, etc.) do not work in Dialogue Manager and they only work with Smart Dates, which is just the number of days since 12/31/1900.

To get around #2, you have to use DATECVT function, for example:

-SET &YESTERDAY=DATECVT(DATEADD(DATECVT(&YYMD,'I8YYMD','YYMD'),'D',-1),'YYMD','I8YYMD');
-SET &MONTHEND=DATECVT(DATEMOV(DATECVT(&YYMD,'I8YYMD','YYMD'),'EOM'),'YYMD','I8YYMD');

The reason that what TCL is doing works is because when he does this:

-SET &FROMDATE = DATECVT(&FROMDATE, 'I8MDYY', 'YMD');

he is converting &FROMDATE to a smart date. That is why he can add 1 to it and it works. You cannot always add 1 to &YYMD and get a valid date.

quote:

Originally posted by TCL:
The code works perfectly well and gives all realistic dates with my environment. I ran the code below:

You're right. The first thing I did was to copy/paste out the pieces that appealed to me and started working on my own version, never really running the original code you provided in it's entirety (there I said it) .

With some minor mods to fit my YYMD formats, this version works as expected.

-***TEST
-SET &FROMDT ='20110220';
-SET &TODT ='20110320';
-*****
-SET &FROMDATE = DATECVT(&FROMDT, 'I8YYMD', 'YYMD');
-SET &TODATE = DATECVT(&TODT, 'I8YYMD', 'YYMD');
-TYPE (&FROMDATE)  (&TODATE)

-SET &NOOFDAYS=DATEDIF(&FROMDATE,&TODATE,'D');
-TYPE &NOOFDAYS

-SET &NEWDATE = &FROMDATE;
-*REPEAT DATE_LOOP WHILE &NEWDATE LE &TODATE;
-REPEAT DATE_LOOP &NOOFDAYS TIMES;
-TYPE &NEWDATE
-*SET &NEWDATE = &NEWDATE + 1;
-SET &NEWDATE = DATEADD(&NEWDATE, 'D', 1);
-SET &NEWDT = EDIT(DATECVT(&NEWDATE, 'YYMD', 'I8YYMD'),'9999/99/99');
-TYPE &NEWDT
-DATE_LOOP 
-SET &NEWDT = EDIT(DATECVT(&NEWDATE, 'YYMD', 'I8YYMD'),'9999/99/99');
-TYPE END: &NEWDATE (&NEWDT)

Let that be a lesson to you kids out there. Before complaining some stolen code doesn't work right, first make sure you stole it all!

- ABT

P.S.

quote:

If you guys read the documentation

But there's just so much of it...

[SOLVED]