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.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

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.


See Also

dateadd , datestring