The next table summarizes the types of arithmetic operations that are valid in a date/time expression. Note that you cannot use a TIME variable in an arithmetic expression. For an explanation of the format symbols used, see "Date, time, and timestamp format specifiers."
Type of Operand 1 | Operator | Type of Operand 2 | Type of Result | Comments |
---|---|---|---|---|
DATE | - | DATE | INT | INT is a number of days. |
DATE | +/- | NUMBER | DATE | NUMBER can be a variable, a literal, or a numeric expression, and is assumed to be a number of days. |
NUMBER | + | DATE | DATE | NUMBER can be a variable, a literal, or a numeric expression, and is assumed to be a number of days. |
TIMESTAMP | - | TIMESTAMP | INTERVAL | See "Using INTERVAL variables" below. |
DATE | - | TIMESTAMP | INTERVAL | See "Using INTERVAL variables" below. |
TIMESTAMP | - | DATE | INTERVAL | See "Using INTERVAL variables" below. |
TIMESTAMP | +/- | INTERVAL | TIMESTAMP | |
INTERVAL | + | TIMESTAMP | TIMESTAMP | |
DATE | +/- | INTERVAL | TIMESTAMP | |
INTERVAL | + | DATE | TIMESTAMP | |
INTERVAL | +/- | INTERVAL | INTERVAL | INTERVALs must be of the same span, that is, either month- or second-span INTERVALs. |
INTERVAL | *// | NUMBER | INTERVAL | For example, you can divide an INTERVAL of 3 years 3 months by 2. The result is truncated to 1 year 7 months. |
The tricky concept here is the INTERVAL type. The INTERVAL comes into play only when expressions involve a TIMESTAMP or an INTERVAL as one of the operands. There are actually two different versions of the INTERVAL type. One stores month-span intervals (intervals greater than one month), and the other stores second-span intervals (intervals less than a month). You must provide a format pattern when you declare an INTERVAL variable, and that format must tell EGL whether the INTERVAL is a month-span or a second-span.
diff01 INTERVAL("yyyy"); diff02 INTERVAL("MM"); diff03 INTERVAL("yyyyMM");
diff04 INTERVAL ("ddhhmmssffff"); diff05 INTERVAL ("hhmmss"); diff06 INTERVAL ("ddhhmm"); diff07 INTERVAL ("ss");
diff08 INTERVAL ("yyyyMMdd"); // illegal diff09 INTERVAL ("MMddhhmmss"); // illegal
//month-span interval diff01 INTERVAL ("yyyyMM"); t1 TIMESTAMP ("yyyyMM") = "200504"; t2 TIMESTAMP ("yyyyMM") = "200604"; diff01 = t2 - t1; // diff01 is "+000100" (1 year) // second-span interval diff02 INTERVAL ("ddhhmmss"); t3 TIMESTAMP ("yyyyMMddhhmm") = "200604201027"; t4 TIMESTAMP ("yyyyMMddhhmm") = "200604201555"; diff02 = t4 - t3; // diff02 is "+00052800" (5 hours, 28 mins)
Platform | Issue |
---|---|
JavaScript generation | The following types are supported: ANY, BIGINT,
BIN (but only in the absence of decimal places), Boolean, DataItem,
DATE, DECIMAL, Delegate, Dictionary, FLOAT, INT, NUM, NUMBER, SMALLFLOAT,
SMALLINT, STRING (but only in the absence of a size limit) , TIME,
TIMESTAMP, NUM, MONEY, Service parts, Interface parts, External types
(stereotype JavaScript), arrays of supported types, and non-structured
Basic, Exception, and SQL Record parts. The following types are not supported: ArrayDictionary, BIN (with decimal places), BLOB, CHAR, CLOB, DBCHAR, HEX, INTERVAL, MBCHAR, NUMC, STRING (with a size limit), PACF, UNICODE, and structured Record parts. |