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:
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.