How to: |
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
DATEMOV works only with full component dates.
DATEMOV(date, 'move-point')
where:
Date
Is the date to be moved. It must be a full component format date (for example, MDYY or YYJUL).
Alphanumeric
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 finds the end day of the current date week
DATEDIF('&YYMD', 'EOW')
and returns 20040326 if today is 2004, March 23rd. Note the use of the system variable &YYMD and natural date representation in the first argument.
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:
WebFOCUS | |
Feedback |