Switch to standard view 
  Sybase logo
 
 
 



Background

Similar to other RDBMSs that use the DATEDIFF and DATEADD functions (for example, Microsoft SQL Server, Adaptive Server Enterprise, and Microsoft Access), in Adaptive Server Anywhere these two functions use integer data types to store or return the time interval. This results in two limitations.

The first limitation involves the interval value returned by:

DATEDIFF ( date-part, date-expression1, date-expression2 )

For the smaller time units used in the date-part parameter, an overflow error occurs if the difference between date-expression1 and date-expression2 exceeds the following intervals:

  • milliseconds 24 days
  • seconds 68 years
  • minutes 4083 years

The second limitation involves the case when the numeric-expression parameter of the function DATEADD ( date-part, numeric-expression, date-expression ) is truncated to be a signed integer. As a result of the truncation, if you use a small time unit for the numeric-expression parameter, the returned value is not correct if the numeric-expression value exceeds the range for signed integers.

Solution

To overcome these limitations, you can make use of user functions. User functions allow you to split the calculation so that portions of it can be calculated using a larger time unit. The following examples of user functions use values in the BIGINT data range, allowing you to extend the supported date ranges.

Date ranges not exceeding 4083 years

The following user functions allow a date range of up to 4083 years - more than enough for general usage.

Instead of DATEDIFF ( second, starttime, endtime ), you can use:

Instead of DATEDIFF ( millisecond, starttime, endtime ), you can use:

Instead of DATEADD ( second, difference, starttime ), you can use:

Instead of DATEADD ( millisecond, difference, starttime ), use:

Date ranges exceeding 4083 years

For ranges that exceed 4083 years, after addressing extreme DATEDIFF and DATEADD calculation issues by upgrading to the necessary build (9.0.2 build 3044 or higher, 9.0.1 build 1994 or higher, 8.0.3 build 5220 or higher, and 8.0.2 build 4521 or higher), you can use the following user functions.

Instead of DATEDIFF ( second, starttime, endtime ), you can use:

Instead of DATEDIFF ( millisecond, starttime, endtime ), you can use:

Instead of DATEADD ( second, difference, starttime ), you can use:

Instead of DATEADD ( millisecond, difference, starttime ), you can use:

This user function allows the full range of date time values (up to 7911-01-01) as documented in the Adaptive Server Anywhere SQL User's Guide. Accuracy of the functions that exceed this range cannot be guaranteed as it is beyond the range supported by Adaptive Server Anywhere.



Back to Top
© Copyright 2010, Sybase Inc.