Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.1 » SQL Anywhere Server - SQL Reference » Using SQL » SQL data types » Date and time data types


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

When a date or time is retrieved as a string, it is retrieved in the format specified by the database options date_format, time_format and timestamp_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.