Retrieving dates and times from the database

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

Using any interface, as a string
Using ODBC, as a TIMESTAMP structure
Using embedded SQL, as a SQLDATETIME structure

Date and times with time zone offsets may 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. For descriptions of these options, see SET OPTION statement.

For information about functions that deal with dates and times, see Date and time functions. 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.

Discuss this page in DocCommentXchange.