DATEDIFF

DATEDIFF takes three arguments: a control string, a start timestamp, and an end timestamp. It returns the integer delta between the part of the two timestamps specified by the control string. Components smaller than the control string are ignored. Components larger than the control string contribute to the result:

Integer DATEDIFF( control Varchar, start Timestamp, end Timestamp )

Examples

The following code:

SELECT ORDERS.ORDERID, ORDERS.SHIPBYDATE, ORDERS.FORECASTSHIPDATE, DATEDIFF('d', ORDERS.SHIPBYDATE, ORDERS.FORECASTSHIPDATE) AS ShipDateDifference

FROM "../Data Sources/MyDatabase/ORDERS.SMA" ORDERS

returns:

1645,1995-05-22 00:00:00.0,1995-06-02 00:00:00.0,11

1340,1995-06-03 00:00:00.0,1995-06-10 00:00:00.0,7

1810,1995-04-12 00:00:00.0,1995-04-27 00:00:00.0,15

…

The following expression:

DATEDIFF('d', CAST('2005-12-31 23:59:59.0' AS TIMESTAMP), CAST('2006-01-01 00:00:00.0' AS TIMESTAMP))

returns 1. The control string d indicates that the difference is in days. The difference between December 31, 2005 and January 1, 2006 is one day. The hours, minutes, and seconds components are ignored.

The following expression:

DATEDIFF('m', CAST('2005-12-31 23:59:59.0' AS TIMESTAMP), CAST('2006-01-01 00:00:00.0' AS TIMESTAMP))

returns 1. The control string m indicates that the difference is in months. The difference between December 31, 2005 and January 1, 2006 is one month. The day, hours, minutes, and seconds components are ignored.

Copyright Actuate Corporation 2012