I am trying to automate a query so that the user does not have to manually enter the date range every time it is run. I'm needing something like the following:

From Date = Today's date - 7 days
To Date = Today's date - 1 day

So basically I'm needing the query to run for the previous week's date range. Any help, as always, is greatly appreciated. Thanks.

Is the data in 'smart date' format in your master file definition? If so, then you just define the two dates you want for the range and do the selection on the date and select based on the two dates. An example

  
TODAYS/YYMD = &YYMD;
YESTERDAY/YYMD = TODAYS - 1;
LASTRUN1/YYMD = TODAYS - 7;

One issue on the use of defined fields in selection though is unless you limit the 'answer set' with other criteria, your whole file will come back then the dates. I'm sure the dialog manager gurus have another method as well.

Good luck.

Brian

Please start with updating your signature,so we know what version you have.

Do you have studied the manuals and special the DM part?
How about the amper values?

You have a basic date that holds today "&YYMD"

-SET &START=DATEADD((DATECVT(&YYMD, I8YYMD, YYMD)), D, -7);
-SET &END=DATEADD((DATECVT(&YYMD, I8YYMD, YYMD)), D, -1);

Leah and Brian,

In Dialogue Manager, you would use the AYMD subroutine.

-SET &TODATE=AYMD(&YYMD,-1,'I8YYMD');
-SET &FRDATE=AYMD(&YYMD,-7,'I8YYMD');
  

DATEADD cannot be used with DM, use AYMD as Ginny states, but in DM the output format should only be I8, not I8YYMD (which is a picky point I know.)

Alan,

According to the Functions manual, the format can be I6, I6YMD, I8, or I8YYMD.

Got it. Thanks everybody.

Brian

Ginny was right, but it is interesting for you to see the difference in result.

-SET &ECHO=ALL;
-SET &START=DATEADD((DATECVT(&YYMD, 'I8YYMD', 'YYMD')), D, -7);
-SET &EIND=DATEADD((DATECVT(&YYMD, 'I8YYMD', 'YYMD')), D, -1);
-SET &TODATE=AYMD(&YYMD,-1,'I8YYMD');
-SET &FRDATE=AYMD(&YYMD,-7,'I8YYMD');
TYPE &START
TYPE &FRDATE
TYPE &EIND
TYPE &TODATE

So try this.

The "start" end "eind" wil give a number that represents the date, can be usefull.

quote:

DATEADD cannot be used with DM

Not quite right Alan, or so it appears.

Works fine on both set-ups here.

-SET &MBEG=DATECVT(DATEADD((DATECVT(&YYMD, I8YYMD, YYMD)), D, -7), YYMD, I8YYMD);
-SET &MEND=DATECVT(DATEADD((DATECVT(&YYMD, I8YYMD, YYMD)), D, -1), YYMD, I8YYMD);
-? &M

T

Tony, I was quoting the documentation here

quote:

The DATEADD function cannot be used with Dialogue Manager. DATEADD requires dates to be in date format; Dialogue Manager interprets a date as alphanumeric or numeric.

. So yes, if you want to convert to and fro with DATECVT, it will work. But AYMD is simpler.

As for the formats for the return in AYMD, the I8YYMD is not really a valid &variable format, as it would be for a DEFINEd or COMPUTEd field.

Alan, accepted. I don't have a manual readily to hand and so did not check What you say makes sense though.

T

IMO, it would be a good addition to the manual to show how to use any of the date functions in Dialogue Manager. I always use AYMD if I'm just wanting to add or subtract days from a date, however, one has to use DATEADD with DATECVT to add Business Days or Week Days to an & variable that is a date in DM.