Name
datediff — get difference of 2 dates
Synopsis
datediff
(
|
in unit string , |
| in date1 datetime , | |
in
date2
datetime
); |
Description
datediff subtracts date1 from date2 and returns the difference as an integer in the specified units.
Parameters
unit
The resulting unit name as a string. May be 'millisecond', 'second', 'minute', 'hour', 'day', 'month', or 'year'
date1
The
datetime
value that will be subtracted from
date2
date2
The
datetime
value
date1
is subtracted from
Return Values
Difference of
date1
and
date2
as an
integer
.
Errors
Table 24.16. Errors signalled by
datediff
| SQLState | Error Code | Error Text | Description |
|---|---|---|---|
| 22023 | DT002 | Bad unit in datediff: >offending unit string< | The unit given was not one of the units listed above. |
Examples
Example 24.71. A simple example
Get hour difference of 1996.10.10 and 1996.10.11.
SQL> SELECT datediff ('hour', stringdate ('1996.10.10'), stringdate ('1996.10.11'));
callret
INTEGER
_________________________________________________________________________
24
1 Rows. -- 57 msec.
Example 24.72. Demo DB example
Get average order processing time in days
SQL> use "Demo";
SQL> SELECT avg (datediff ('day', "OrderDate", "ShippedDate"))
as "Avg_Processing_Time" from "Orders" where "ShippedDate" is not null;
Avg_Processing_Time
INTEGER
_________________________________________________________________________
8
1 Rows. -- 11 msec.