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