I'm using the following in a procedure:

-SET &EDDAY = DATEADD (&YYMD, 'D', (-1));
-SET &STDAY = DATEADD (&YYMD, 'M', (-2));

When I echo the output, I see:

  
 -SET &EDDAY = DATEADD (20080819, 'D', (-1));
 -TYPE 20080818
 20080818
 -SET &STDAY = DATEADD (20080819, 'M', (-2));
 -TYPE ********
 ********

The SET for &STDAY seems to think the output is too large for the field type, but I'm not sure how this is possible for YYMD.

This is using a SQL Server DB with DATETIME set to OFF in edasprof, so HYYMDI fields shouldn't be the problem. Anyone know why this is happening?

The DATEADD subroutine is for smart dates and Dialogue Manager dates, by definition, are legacy dates. Therefore, you should use legacy date subroutines to reformat.

-SET &EDDAY = AYMD(&YYMD, -1,'I8YYMD');
-SET &CURRYM= EDIT(&YYMD,'999999');
-SET &CURRDY= EDIT(&YYMD,'$$$$$$99');
-SET &STYM = AYM(&CURRYM,-2,'I6YYM');
-SET &STDAY=&STYM||&CURRDY;
-TYPE &EDDAY &STDAY

You can play with this to tweak to your own requirements.

Or you can use DATECVT to reformat the dates. Use the search button above and search the forum for the DATECVT subroutine. There are lots and lots of posts on this.

I'm all too familiar with the conversions, just not clear on why they're necessary here when the same function seems to work fine in the first case but not the second.

Try This

-SET &EDDAY = DATEADD ('080819', 'D', (-1));
-TYPE &EDDAY
-SET &STDAY = DATEADD ('080819', 'M', (-2));
-TYPE &STDAY



Expected format is YMD not YYMD.


Sayed

DATEADD requires an "offset" (or "smart") date value in arg 1.

To operate on &YYMD, which contains the date in calendar form, use DATECVT to transform it into the equivalent offset value, apply DATEADD to that, and apply DATECVT again to regain calendar-date format.

Here it is step-by-step, and combined as a one-liner:

-SET &SDATE1=&YYMD;
-SET &SDATE2=DATECVT(&SDATE1,'I8YYMD','YYMD');
-SET &SDATE3=DATEADD(&SDATE2,'M',-2);
-SET &SDATE4=DATECVT(&SDATE3,'YYMD','I8YYMD');

-SET &SDATE=DATECVT( DATEADD( DATECVT(&YYMD,'I8YYMD','YYMD') ,'M',-2) ,'YYMD','I8YYMD');

-? &SDATE


 CURRENTLY DEFINED & VARIABLES STARTING WITH 'SDATE':
 &SDATE        = 20080619
 &SDATE1       = 20080819
 &SDATE2       = 39313
 &SDATE3       = 39252
 &SDATE4       = 20080619

Why did subtracting 1 day to get the end-date seem to work? My guess is, either the "D" code is forgiving where the 'M' code is not; or more likely 20080819 (the value in &YYMD) was interpreted as an offset and nature took its course: subtracting one day gives an offset date of one less, which looks like the calendar date you want. But that will not work crossing a month boundary (try it with &YYMD=20080901)

Sayed, that helps with the field size but doesn't give correct values, which seems to be the case with any workaround I've tried:

-SET &EDDAY = DATEADD (080819, 'D', (-1));
-TYPE EDDAY 80818
EDDAY 80818
-SET &STDAY = DATEADD (080819, 'M', (-2));
-TYPE STDAY 80760
STDAY 80760


I guess I'll have to stick with DATECVT. It would nice to understand why it works the first time without a conversion but not the second, but I guess that's a matter of the processing under the hood.

quote:

Why did subtracting 1 day to get the end-date seem to work? My guess is, either the "D" code is forgiving where the 'M' code is not; or more likely 20080819 (the value in &YYMD) was interpreted as an offset and nature took its course: subtracting one day gives an offset date of one less, which looks like the calendar date you want. But that will not work crossing a month boundary (try it with &YYMD=20080901)

Ah, that makes sense. I was trying to get around using a conversion but I guess that's not an option.

baltes --

See my post above. I guess we crossed in the mail.


Why would the DATEADD call, with &YYMD as first argument, work for adding Days but overflow for Months?

To compute DATEADD(smart_date, 'M', months) the DATEADD function has to determine the calendar components of smart_date, in order to figure out the adjusted date, and then convert that back to offset form. That fails when the input smart_date valus is out of reasonable range (&YYMD = offset of +20080819 = calendar date 568800530 = May 30 of year 56,880!). The function is not set up to reconvert such a far-off date back to offset form, and the output is a garbage value (which overflows when displayed as an 8-digit integer).

On the other hand, DATEADD(smart_date, 'D', days) can always be computed by blindly adding the increment, since both the smart_date value and the increment value share the same granularity. So DATEADD merrily adds -1 to 20,080,819 and gives you 20,080,818, which looks like yesterday's date. -- But test your code with -SET &YYMD=20080901; and see what happens.