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
Phrases
RDF data
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 & Internet
XML
XPATH & XQUERY

Functions Index

datediff

get difference of 2 dates
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
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