DATEMOV: Moving a Date to a Significant Point

How to:

Move a Date to a Significant Point

Available Languages: reporting, Maintain

The DATEMOV function moves a date to a significant point on the calendar.

Note: Using the beginning of week point (BOW) will always return Monday, and using the end of week point (EOW) will always return Friday. Also, if the date used with the DATEMOV function falls on Saturday or Sunday, the actual date used by the function will be the moved forward to the next Monday. If you do not want to do the calculation by moving the date from Saturday or Sunday to Monday, or if you want the BOW to be Sunday and the EOW to be Saturday, you can use the DTRUNC function.

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.

Syntax: How to Move a Date to a Significant Point

DATEMOV(date, 'move-point')

where:

date

Date

Is the date to be moved. It must be a full component format date (for example, MDYY or YYJUL).

move-point

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:

EOM, which is the end of month.
BOM, which is the beginning of month.
EOQ, which is the end of quarter.
BOQ, which is the beginning of quarter.
EOY, which is the end of year.
BOY, which is the beginning of year.
EOW, which is the end of week.
BOW, which is the beginning of week.
NWD, which is the next weekday.
NBD, which is the next business day.
PWD, which is the prior weekday.
PBD, which is the prior business day.
WD-, which is a weekday or earlier.
BD-, which is a business day or earlier.
WD+, which is a weekday or later.
BD+, which is a business day or later.

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.

Example: Returning the Next Business Day

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

Example: Using a DEFINE FUNCTION to Move a Date to the Beginning of the Week

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:

Example: Determining Significant Points for a Date (Reporting)

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

Example: Determining the End of the Week (Reporting)

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

Example: Determining the End of the Week (Maintain)

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