Available Languages: reporting, Maintain
The DATEMOV function moves a date to a significant point on the calendar.
Since Dialogue Manager interprets a date as alphanumeric or numeric, and DATEMOV requires a standard date stored as an offset from the base date, do not use DATEMOV with Dialogue Manager unless you first convert the variable used as the input date to an offset from the base date. For example, the following converts the integer legacy date 20050131 to a smart date, adds one month, and converts the result to an alphanumeric legacy date:
-SET &STRT=DATECVT(20050131,'I8YYMD', 'YYMD'); -SET &NMT=DATEADD(&STRT,'M',1); -SET &NMTA=DATECVT(&NMT,'YYMD','A8MTDYY'); -TYPE A MONTH FROM 20050131 IS &NMTA
The output shows that the DATEADD function added the actual number of days in the month of February to get to the end of the month from the end of January:
A MONTH FROM 20050131 IS 02282005
For more information, see Calling a Function From a Dialogue Manager Command.
DATEMOV works only with full component dates.
Is the date to be moved. It must be a full component format date (for example, MDYY or YYJUL).
Is the significant point the date is moved to enclosed in single quotation marks ('). An invalid point results in a return code of zero. Valid values are:
A business day calculation is affected by the BUSDAYS and HDAY parameter settings.
Note that when the DATEADD function calculates the next or previous business day or work day, it always starts from a business day or work day. So if the actual day is Saturday or Sunday, and the request wants to calculate the next business day, the function will use Monday as the starting day, not Saturday or Sunday, and will return Tuesday as the next business day. Similarly, when calculating the previous business day, it will use the starting day Friday, and will return Thursday as the previous business day.
To avoid skipping a business day or work day, use DATEMOV. To return the next business or work day, use BD- or WD- to first move to the previous business or work day (if it is already a business day or work day, it will not be moved). Then use DATEADD to move to the next business or work day. If you want to return the previous business or work day, first use BD+ or WD+ to move to the next business or work day (if it is already the correct type of day, it will not be moved). Then use DATEADD to return the previous business or work day.
Note: DATEMOV does not use an output argument. It uses the format of the date argument for the result. As long as the result is a full component date, it can be assigned only to a full component date field or to an integer field.
This example shows why you may need to use DATEMOV to get the correct result.
The following request against the GGSALES data source uses the BD (Business Day) move point against the DATE field. First DATE is converted to a smart date, then DATEADD is called with the BD move-point:
DEFINE FILE GGSALES DT1/WMDYY=DATE; DT2/WMDYY = DATEADD(DT1 ,'BD',1); DAY/Dt = DT1; END TABLE FILE GGSALES SUM DT1 DT2 BY DT1 NOPRINT WHERE RECORDLIMIT EQ 10 END
When the date is on a Saturday or Sunday on the output, the next business day is returned as a Tuesday. This is because before doing the calculation, the original date was moved to a business day:
DT1 DT2 --- --- SUN, 09/01/1996 TUE, 09/03/1996 FRI, 11/01/1996 MON, 11/04/1996 SUN, 12/01/1996 TUE, 12/03/1996 SAT, 03/01/1997 TUE, 03/04/1997 TUE, 04/01/1997 WED, 04/02/1997 THU, 05/01/1997 FRI, 05/02/1997 SUN, 06/01/1997 TUE, 06/03/1997 MON, 09/01/1997 TUE, 09/02/1997 WED, 10/01/1997 THU, 10/02/1997
In the following version of the request, DATEMOV is called to make sure the starting day is a business day. The move point specified in the first call is BD- which only moves the date to the prior business day if it is not already a business day. The call to DATEADD then uses the BD move point to return the next business day:
DEFINE FILE GGSALES DT1/WMDYY=DATE; DT1A/WMDYY=DATEMOV(DT1, 'BD-'); DT2/WMDYY = DATEADD(DT1A,'BD',1); DAY/Dt = DT1; END TABLE FILE GGSALES SUM DT1 DT1A DT2 BY DT1 NOPRINT WHERE RECORDLIMIT EQ 10 END
On the output, the next business day after a Saturday or Sunday is now returned as Monday:
DT1 DT1A DT2 --- ---- --- SUN, 09/01/1996 FRI, 08/30/1996 MON, 09/02/1996 FRI, 11/01/1996 FRI, 11/01/1996 MON, 11/04/1996 SUN, 12/01/1996 FRI, 11/29/1996 MON, 12/02/1996 SAT, 03/01/1997 FRI, 02/28/1997 MON, 03/03/1997 TUE, 04/01/1997 TUE, 04/01/1997 WED, 04/02/1997 THU, 05/01/1997 THU, 05/01/1997 FRI, 05/02/1997 SUN, 06/01/1997 FRI, 05/30/1997 MON, 06/02/1997 MON, 09/01/1997 MON, 09/01/1997 TUE, 09/02/1997 WED, 10/01/1997 WED, 10/01/1997 THU, 10/02/1997
The following DEFINE FUNCTION named BOWK takes a date and the name of the day you want to consider the beginning of the week and returns a date that corresponds to the beginning of the week:
DEFINE FUNCTION BOWK(THEDATE/MDYY,WEEKSTART/A10) DAYOFWEEK/W=THEDATE; DAYNO/I1=IF DAYOFWEEK EQ 7 THEN 0 ELSE DAYOFWEEK; FIRSTOFWK/I1=DECODE WEEKSTART('SUNDAY' 0 'MONDAY' 1 'TUESDAY' 2 'WEDNESDAY' 3 'THURSDAY' 4 'FRIDAY' 5 'SATURDAY' 6 'SUN' 0 'MON' 1 'TUE' 2 'WED' 3 'THU' 4 'FRI' 5 'SAT' 6); BOWK/MDYY=IF DAYNO GE FIRSTOFWK THEN THEDATE-DAYNO+FIRSTOFWK ELSE THEDATE-7-DAYNO+FIRSTOFWK; END
The following request uses the BOWK function to use return a date (DT2) that corresponds to the beginning of the week for each value of the DT1 field:
DEFINE FILE GGSALES DT1/WMDYY=DATE; DT2/WMDYY = BOWK(DT1 ,'SUN'); END TABLE FILE GGSALES SUM DT1 DT2 BY DT1 NOPRINT WHERE RECORDLIMIT EQ 10 ON TABLE SET PAGE NOLEAD END
The output is shown in the following image:
The BUSDAYS parameter sets the business days to Monday, Tuesday, Wednesday, and Thursday. DATECVT converts the legacy date HIRE_DATE to the date format YYMD and provides date display options. DATEMOV then determines significant points for HIRE_DATE.
SET BUSDAY = _MTWT__ TABLE FILE EMPLOYEE PRINT COMPUTE NEW_DATE/YYMD = DATECVT(HIRE_DATE, 'I6YMD', 'YYMD'); AND COMPUTE NEW_DATE/WT = DATECVT(HIRE_DATE, 'I6YMD', 'WT'); AS 'DOW' AND COMPUTE NWD/WT = DATEMOV(NEW_DATE, 'NWD'); AND COMPUTE PWD/WT = DATEMOV(NEW_DATE, 'PWD'); AND COMPUTE WDP/WT = DATEMOV(NEW_DATE, 'WD+'); AS 'WD+' AND COMPUTE WDM/WT = DATEMOV(NEW_DATE, 'WD-'); AS 'WD-' AND COMPUTE NBD/WT = DATEMOV(NEW_DATE, 'NBD'); AND COMPUTE PBD/WT = DATEMOV(NEW_DATE, 'PBD'); AND COMPUTE WBP/WT = DATEMOV(NEW_DATE, 'BD+'); AS 'BD+' AND COMPUTE WBM/WT = DATEMOV(NEW_DATE, 'BD-'); AS 'BD-' BY LAST_NAME NOPRINT HEADING "Examples of DATEMOV" "Business days are Monday, Tuesday, Wednesday, + Thursday " " " "START DATE.. | MOVE POINTS..........................." WHERE DEPARTMENT EQ 'MIS'; END
The output is:
Examples of DATEMOV Business days are Monday, Tuesday, Wednesday, + Thursday START DATE.. | MOVE POINTS........................... NEW_DATE DOW NWD PWD WD+ WD- NBD PBD BD+ BD- -------- --- --- --- --- --- --- --- --- --- 1982/04/01 THU FRI WED THU THU MON WED THU THU 1981/11/02 MON TUE FRI MON MON TUE THU MON MON 1982/04/01 THU FRI WED THU THU MON WED THU THU 1982/05/01 SAT TUE THU MON FRI TUE WED MON THU 1981/07/01 WED THU TUE WED WED THU TUE WED WED 1981/07/01 WED THU TUE WED WED THU TUE WED WED
DATEMOV determines the end of the week for each date in NEW_DATE and stores the result in EOW:
TABLE FILE EMPLOYEE PRINT FIRST_NAME AND COMPUTE NEW_DATE/YYMDWT = DATECVT(HIRE_DATE, 'I6YMD', 'YYMDWT'); AND COMPUTE EOW/YYMDWT = DATEMOV(NEW_DATE, 'EOW'); BY LAST_NAME WHERE DEPARTMENT EQ 'PRODUCTION'; END
The output is:
LAST_NAME FIRST_NAME NEW_DATE EOW --------- ---------- -------- --- BANNING JOHN 1982 AUG 1, SUN 1982 AUG 6, FRI IRVING JOAN 1982 JAN 4, MON 1982 JAN 8, FRI MCKNIGHT ROGER 1982 FEB 2, TUE 1982 FEB 5, FRI ROMANS ANTHONY 1982 JUL 1, THU 1982 JUL 2, FRI SMITH RICHARD 1982 JAN 4, MON 1982 JAN 8, FRI STEVENS ALFRED 1980 JUN 2, MON 1980 JUN 6, FRI
DATEMOV determines the end of the week for each date:
MAINTAIN COMPUTE X/YYMDWT='20020717'; COMPUTE Y/YYMDWT=DATEMOV(X, 'EOW', Y); TYPE "<<X <<Y END OF WEEK " END
The result is:
2002/07/17, WED 2002/07/19, FRI END OF WEEK