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.

The date and time functions 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).

These functions are Transact-SQL date and time functions. They allow an alternative way of accessing and manipulating date and time functions:

DATEADD
DATEDIFF
DATENAME
DATEPART
GETDATE

You should convert arguments to date functions to dates before using them. For example, this is incorrect:

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

This is correct:

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

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

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

The following statement, processed by SAP Sybase IQ, uses a different starting point for the WEEKS function and returns a different result than 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 SAP Sybase IQ, the imaginary date 0000-01-01, is chosen to produce the most efficient date calculations and is consistent across various data parts. SQL Anywhere does not have a single starting date. The following statements, the first processed by SQL Anywhere, the second by SAP Sybase IQ, both return the answer 12:

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

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

However, also consider these statements:

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

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

The first, processed by SQL Anywhere, yields the value 307, but the second, processed by SAP Sybase IQ, yields 166.

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

Note: Create a dummy table with only one column and row. You can then reference this table in the FROM clause for any SELECT statement that uses date or time functions, thus ensuring processing by SAP Sybase IQ, and consistent results.