LDIFF
Purpose
Returns the portion of the first Period expression that exists before the beginning of the second Period expression when the Period expressions overlap.
When the Period expressions overlap but there is no portion of the first Period expression before the beginning of the second Period expression or the Period expressions do not overlap, the function returns NULL. If either Period expression is NULL, LDIFF returns NULL.
Syntax
where:
Syntax element... |
Specifies... |
period_expression |
any expression that evaluates to a Period data type. Note: The Period expressions specified must be comparable. Implicit casting to a Period data type is not supported. |
Result Value
Assuming p1 and p2 are Period expressions and the result element type as determined above is rt, the result of p1 LDIFF p2 is as follows if p1 OVERLAPS p2 is TRUE:
PERIOD(
CASE WHEN CAST(BEGIN(p1) AS rt) < CAST(BEGIN(p2) AS rt)
THEN CAST(BEGIN(p1) AS rt)
ELSE NULL END,
CASE WHEN CAST(BEGIN(p1) AS rt) < CAST(BEGIN(p2) AS rt)
THEN CAST(BEGIN(p2) AS rt)
ELSE NULL END)
Internally, Period values are saved in UTC and the OVERLAPS operator is evaluated using these UTC represented formats and the LDIFF operation is performed if they overlap.
Format and Title
The format is the default format for the resulting Period data type.
Error Conditions
If either expression is not a Period expression, an error is reported.
If the Period expressions are not comparable, an error is reported.
Example
LDIFF is used to find the left difference of the first Period expression with the second Period expression.
SELECT ename, period2 LDIFF period1 FROM employee;
Assume the query is executed on the following employee table where period1 and period2 are PERIOD(DATE) columns:
ename period1 period2
----- ---------------------------- ----------------------------
Adams ('2005-02-03', '2006-02-03') ('2005-02-03', '2006-02-03')
Mary ('2005-04-02', '2006-01-03') ('2005-02-03', '2006-02-03')
Jones ('2004-01-02', '2004-03-05') ('2002-03-05', '2004-10-07')
Randy ('2006-01-02', '2007-03-05') ('2004-03-07', '2005-10-07')
Simon ? ('2005-02-03', '2005-07-27')
The result is:
ename (period2 LDIFF period1)
----- ----------------------------
Adams ?
Mary ('2005-02-03', '2005-04-02')
Jones ('2002-03-05', '2004-01-02')
Randy ?
Simon ?