# 22. Virtuoso Functions Guide - datediff

Chapter Contents | Prev | Next
Keyword Search:
www.openlinksw.com
docs.openlinksw.com
Book Home
Contents
Preface
Virtuoso Functions Guide
Administration
Aggregate Functions
Array Manipulation
BPEL APIs
Backup
Compression
Cursor
Date & Time Manipulation
datestring, ...
curdate
dateadd
datediff
datestring_gmt
dayname
dayofmonth
dayofweek
dayofyear
dt_set_tz
get_timestamp
getdate
hour
minute
month
monthname
msec_time
now
quarter
second
stringdate
stringtime
timezone
week
Debug
Dictionary Manipulation
Encoding & Decoding
File Manipulation
Free Text
Hashing / Cryptographic
LDAP
Locale
Mail
Miscellaneous
Number
Remote SQL Data Source
Replication
SOAP
SQL
String
Transaction
Type Mapping
UDDI
User Defined Types & The CLR
Virtuoso Java PL API
Virtuoso Server Extension Interface (VSEI)
Web Server & Internet
XML
XPATH & XQUERY
Functions Index

## datediff

get difference of 2 dates
datediff (in unit string, in date1 datetime, in date2 datetime);
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.

Description

datediff subtracts date1 from date2 and returns the difference as an integer in the specified units.

Errors

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

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