Date and time functions

Date and time functions perform conversion, extraction, or manipulation operations on date and time data types and can return date and time information.

Sybase IQ provides two classes of date and time functions. While they can be used interchangeably, they have different styles. One set is Transact-SQL compatible.

The date and time functions listed in Syntax 1 allow manipulation of time units. Most time units (such as MONTH) have four functions for time manipulation, although only two names are used (such as MONTH and MONTHS).

The functions listed in Syntax 2 are the Transact-SQL date and time functions. They allow an alternative way of accessing and manipulating date and time functions.

Arguments to date functions should be converted to dates before being used. Thus, the following is not correct:

days ( '1995-11-17', 2 )

But the following is correct.

days ( date( '1995-11-17' ), 2 )

Sybase IQ does not have the same constants or data type promotions as Adaptive Server Anywhere, with which it shares a common user interface. If you issue a SELECT statement without a FROM clause, the statement is passed through to Adaptive Server Anywhere. The following statement is handled exclusively by Adaptive Server Anywhere:

SELECT WEEKS(‘1998/11/01’);

The following statement, processed by Sybase IQ, uses a different starting point for the WEEKS function and returns a different result from the statement above:

SELECT WEEKS(‘1998/11/01’) FROM iq_dummy;

Consider another example. The MONTHS function returns the number of months since an “arbitrary starting date”. The “arbitrary starting date” of Sybase IQ, the imaginary date 0000-01-01, is chosen to produce the most efficient date calculations and is consistent across various data parts. Adaptive Server Anywhere doesn't have a single starting date. The following statements, the first processed by Adaptive Server Anywhere, the second by Sybase IQ, both return the answer 12:

SELECT MONTHS('0001/01/01');

SELECT MONTHS('0001/01/01') FROM iq_dummy;

For the most consistent results, therefore, you should always include the table name in the FROM clause whether you need it or not.

On the other hand, these statements yield the values 307 (Adaptive Server Anywhere) and 166 (Sybase IQ) respectively:

SELECT DAYS('0001/01/01');

SELECT DAYS('0001/01/01') FROM iq_dummy;

For the most consistent results, therefore, you should always include the table name in the FROM clause whether you need it or not.