Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » UltraLite - Database Management and Reference » UltraLite SQL Function Reference » Alphabetical list of functions

DATEADD function [Date and time] Next Page

DATEDIFF function [Date and time]


Returns the interval between two dates.

Syntax

DATEDIFF( date-part, date-expression-1, date-expression-2 )

date-part :
year | quarter | month | week | day | dayofyear | hour | minute | second | millisecond

Parameters

date-part    Specifies the date part in which the interval is to be measured. Choose one of the date objects listed above. For a complete list of date parts, see Date parts.

date-expression-1    The starting date for the interval. This value is subtracted from date-expression-2 to return the number of date-parts between the two arguments.

date-expression-2    The ending date for the interval. Date-expression-1 is subtracted from this value to return the number of date-parts between the two arguments.

Remarks

This function calculates the number of date parts between two specified dates. The result is a signed integer value equal to (date2 - date1), in date parts.

The DATEDIFF function results are truncated, not rounded, when the result is not an even multiple of the date part.

When you use day as the date part, the DATEDIFF function returns the number of midnights between the two times specified, including the second date but not the first.

When you use month as the date part, the DATEDIFF function returns the number of first-of-the-months between two dates, including the second date but not the first.

When you use week as the date part, the DATEDIFF function returns the number of Sundays between the two dates, including the second date but not the first.

For the smaller time units there are overflow values:

The function returns an overflow error if you exceed these limits.

Standards and compatibility
Example

The following statement returns 1.

SELECT DATEDIFF( hour, '4:00AM', '5:50AM' );

The following statement returns 102.

SELECT DATEDIFF( month, '1987/05/02', '1995/11/15' );

The following statement returns 0.

SELECT DATEDIFF( day, '00:00', '23:59' );

The following statement returns 4.

SELECT DATEDIFF( day,
   '1999/07/19 00:00',
   '1999/07/23 23:59' );

The following statement returns 0.

SELECT DATEDIFF( month, '1999/07/19', '1999/07/23' );

The following statement returns 1.

SELECT DATEDIFF( month, '1999/07/19', '1999/08/23' );