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.15. 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.72. 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.73. 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.