Retrieval of dates and times from the database

Dates and times can be retrieved from the database in one of the following ways:

Using any interface, as a string
Using ODBC or OLE DB, as a binary value (using an ODBC TIMESTAMP_STRUCT structure for example)
Using embedded SQL, as a SQLDATETIME structure

Date and time of day with a time zone offset can be retrieved from the database as a string only.

When a date or time, with or without a time zone offset, is retrieved as a string, it is retrieved in the format specified by the database options date_format, time_format, timestamp_format, and timestamp_with_time_zone_format.

The following arithmetic operators are allowed on dates:

timestamp + integer Add the specified number of days to a date or timestamp.
timestamp - integer Subtract the specified number of days from a date or timestamp.
date - date Compute the number of days between two dates or timestamps.
date + time Create a timestamp combining the given date and time.

Leap years

SQL Anywhere uses a globally accepted algorithm for determining which years are leap years. Using this algorithm, a year is considered a leap year if it is divisible by four, unless the year is a century date (such as the year 1900), in which case it is a leap year only if it is divisible by 400.

SQL Anywhere handles all leap years correctly. For example, the following SQL statement results in a return value of "Tuesday":

SELECT DAYNAME('2000-02-29');

SQL Anywhere accepts February 29, 2000—a leap year—as a date, and using this date determines the day of the week.

However, the following statement is rejected by SQL Anywhere:

SELECT DAYNAME('2001-02-29');

This statement results in an error (cannot convert '2001-02-29' to a date) because February 29th does not exist in the year 2001.

Retrieval of dates and times from the database

Leap years

See also