Date, Time, Datetime, and Timestamp Data Types

Although Adaptive Server Enterprise, SQL Anywhere and SAP Sybase IQ all support some form of date and time data, there are some differences.

SQL Anywhere and SAP Sybase IQ support the 4-byte date and time data types.
Adaptive Server Enterprise supports an 8-byte datetime type, and timestamp as a user-defined data type (domain) implemented as binary (8).
SQL Anywhere and SAP Sybase IQ support an 8-byte timestamp type, and an 8-byte datetime domain implemented as timestamp. The millisecond precision of the SQL Anywhere/SAP Sybase IQ datetime data type differs from that of Adaptive Server Enterprise.

Display formats for dates have different defaults:

Adaptive Server Enterprise defaults to displaying dates in the format “MMM-DD-YYYY” but can be changed by setting an option.
SQL Anywhere and SAP Sybase IQ default to the ISO “YYYY-MM-DD” format but can be changed by setting an option.

Time conversions are as follows:

Adaptive Server Enterprise varies the way it converts time stored in a string to an internal time, depending on whether the fraction part of the second was delimited by a colon or a period.
SQL Anywhere and SAP Sybase IQ convert times in the same way, regardless of the delimiter.

When you insert a time into a DATETIME column:

Adaptive Server Enterprise and SAP Sybase IQ default to supplying 1st January 1900.
SQL Anywhere defaults to supplying the current date.

TIME and DATETIME values retrieved from an Adaptive Server Enterprise database change when inserted into a SAP Sybase IQ table with a DATETIME column using INSERT…LOCATION. The INSERT…LOCATION statement uses Open Client, which has a DATETIME precision of 1/300 of a second.

For example, assume that the following value is stored in a table column in an Adaptive Server Enterprise database:

2004-11-08 10:37:22.823

When you retrieve and store it in a SAP Sybase IQ table using INSERT...LOCATION, the value becomes:

2004-11-08 10:37:22.823333