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.

These tables list the date and time functions and their parameters.

Syntax 1

Date and time functions
Date and time functions	Parameters
DATE	( `expression` )
DATECEILING	(`date-part`, `datetime-expr`, `[multiple-expr]`)
DATEFLOOR	(`date-part`, `datetime-expr`, `[multiple-expr]`)
DATEFORMAT	( `datetime-expr`, `string-expr` )
DATENAME	( `date-part`, `date-expr` )
DATEROUND	(`date-part`, `datetime-expr`, `[multiple-expr]`)
DATETIME	( `expression` )
DAY	( `date-expr` )
DAYNAME	( `date-expr` )
DAYS	( `date-expr` )
DAYS	( `date-expr`, `date-expr` )
DAYS	( `date-expr`, `integer-expr` )
DOW	( `date-expr` )
HOUR	( `datetime-expr` )
HOURS	( `datetime-expr` )
HOURS	( `datetime-expr`, `datetime-expr` )
HOURS	( `datetime-expr`, `integer-expr` )
ISDATE	( `string` )
MINUTE	( `datetime-expr` )
MINUTES	( `datetime-expr` )
MINUTES	( `datetime-expr`, `datetime-expr` )
MINUTES	( `datetime-expr`, `integer-expr` )
MONTH	( `date-expr` )
MONTHNAME	( `date-expr` )
MONTHS	( `date-expr` )
MONTHS	( `date-expr`, `date-expr` )
MONTHS	( `date-expr`, `integer-expr` )
NOW	( * )
QUARTER	( `date-expr` )
SECOND	( `datetime-expr` )
SECONDS	( `datetime-expr` )
SECONDS	( `datetime-expr`, `datetime-expr` )
SECONDS	( `datetime-expr`, `integer-expr` )
TODAY	( * )
WEEKS	( `date-expr` )
WEEKS	( `date-expr`, `date-expr` )
WEEKS	( `date-expr`, `integer-expr` )
YEAR	( `date-expr` )
YEARS	( `date-expr` )
YEARS	( `date-expr`, `date-expr` )
YEARS	( `date-expr`, `integer-expr` )
YMD	( `year-num`, `month-num`, `day-num` )

Syntax 2

Transact-SQL-compatible date and time functions
Transact-SQL compatible date and time functions	Parameters
DATEADD	( `date-part,` `numeric-expression,` `date-expr` )
DATEDIFF	( `date-part`, `date-expr1`, `date-expr2` )
DATENAME	( `date-part`, `date-expr` )
DATEPART	( `date-part`, `date-expr` )
GETDATE	()

Description

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

The date and time functions listed for 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 for Syntax 2 are the Transact-SQL date and time functions. They allow an alternative way of accessing and manipulating date and time functions.

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

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

This is correct:

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

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 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 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 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 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 Sybase IQ, and consistent results.