HADD: Incrementing a Date-Time Value

Syntax: How to Increment a Date-Time Value

HADD(datetime, 'component', increment, length, output)

where:

datetime

Date-time

Is the date-time value to be incremented, the name of a date-time field that contains the value, or an expression that returns the value.

component

Alphanumeric

Is the name of the component to be incremented enclosed in single quotation marks.

Note: WEEKDAY is not a valid component for HADD.

increment

Integer

Is the number of units (positive or negative) by which to increment the component, the name of a numeric field that contains the value, or an expression that returns the value.

length

Integer

Is the number of characters returned. Valid values are:

8 indicates a date-time value that includes one to three decimal digits (milliseconds).
10 indicates a date-time value that includes four to six decimal digits (microseconds).
12 indicates a date-time value that includes seven to nine decimal digits (nanoseconds).

output

Date-time

Is the field that contains the result, or the format of the output value enclosed in single quotation marks. This field must be in date-time format (data type H).

Example: Incrementing the Month Component of a Date-Time Field (Reporting)

HADD adds two months to each value in TRANSDATE and stores the result in ADD_MONTH. If necessary, the day is adjusted so that it is valid for the resulting month.

TABLE FILE VIDEOTR2
PRINT CUSTID TRANSDATE AS 'DATE-TIME' AND COMPUTE
ADD_MONTH/HYYMDS = HADD(TRANSDATE, 'MONTH', 2, 8, 'HYYMDS');
WHERE DATE EQ 2000;
END

The output is:

CUSTID  DATE-TIME         ADD_MONTH
------  ---------         ---------
1237    2000/02/05 03:30  2000/04/05 03:30:00
1118    2000/06/26 05:45  2000/08/26 05:45:00

Example: Incrementing the Month Component of a Date-Time Field (Maintain)

HADD adds two months to the DT1 field:

MAINTAIN FILE DATETIME
FOR 1 NEXT ID DT1 INTO DTSTK
COMPUTE
NEW_DATE/HYYMDS = HADD(DTSTK.DT1, 'MONTH', 2,10, NEW_DATE);
TYPE "DT1 IS: <DTSTK(1).DT1 "
TYPE "NEW_DATE IS: <NEW_DATE "

The result is:

DT1 IS: 2000/1/1 02:57:25
NEW_DATE IS: 2000/3/1 02:57:25
TRANSACTIONS: COMMITS = 1 ROLLBACKS = 0
SEGMENTS : INCLUDED = 0 UPDATED = 0 DELETED = 0

Example: Converting Unix (Epoch) Time to a Date-Time Value

Unix time (also known as Epoch time) defines an instant in time as the number of seconds that have elapsed since 00:00:00 Coordinated Universal Time (UTC), Thursday, 1 January 1970, not counting leap seconds.

The following DEFINE FUNCTION takes a number representing epoch time and converts it to a date-time value by using the HADD function to add the number of seconds represented by the input value in epoch time to the epoch base date:

DEFINE FUNCTION UNIX2GMT(INPUT/I9)
   UNIX2GMT/HYYMDS = HADD(DT(1970 JAN 1),'SECONDS',INPUT,8,'HYYMDS');
END

The following request uses this DEFINE FUNCTION to convert the epoch time 1449068652 to a date-time value:

DEFINE FILE GGSALES
INPUT/I9=1449068652;
OUTDATE/HMTDYYSb = UNIX2GMT(INPUT);
END
TABLE FILE GGSALES
PRINT DATE NOPRINT INPUT OUTDATE
WHERE RECORDLIMIT EQ 1
ON TABLE SET PAGE NOLEAD
END

The output is shown in the following image: