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
Table24.17.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
Example24.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.
Example24.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.