Year 2000 compliance

Chapter 41. SQL Anywhere Data Types
Year 2000 compliance

The problem of handling dates, in particular year values beyond the year 2000, is a significant issue for the computer industry.

This section examines the year 2000 compliance of SQL Anywhere. It illustrates how date values are handled internally by SQL Anywhere, and how SQL Anywhere handles ambiguous date information, such as the conversion of a two digit year string value.

Users of Sybase SQL Anywhere and its predecessors can be assured that dates are handled and stored internally in a manner not adversely effected by the transition from the 20th century to the 21st century.

Consider the following measurements of SQL Anywhere's year 2000 compliance:

SQL Anywhere always returns correct values for any legal arithmetic and logical operations on dates, regardless of whether the calculated values span different centuries.
At all times SQL Anywhere's internal storage of dates explicitly includes the century portion of a year value.
SQL Anywhere's operation is unaffected by any return value, including the current date.
Date values can always be outputted in full century format.

Many of the date-related topics summarized in this section are explained in greater detail in other parts of the documentation. Links to relevant sections are provided.

How dates are stored

Dates containing year values are used internally and stored in SQL Anywhere databases using either of the following data types:

Data type Contains Stored in Range of possible values

DATE Calendar date (year, month, day) 4-bytes 0001-01-01 to 9999-12-31

TIMESTAMP Time stamp (year, month, day, hour minute, second, and fraction of second accurate to 6 decimal places.) 8-bytes 0001-01-01 to 9999-12-31 (precision of time portion of TIMESTAMP is dropped prior to 1600-02-28 23:59:59 and after 7911-01-01 00:00:00)

Data type	Contains	Stored in	Range of possible values
DATE	Calendar date (year, month, day)	4-bytes	0001-01-01 to 9999-12-31
TIMESTAMP	Time stamp (year, month, day, hour minute, second, and fraction of second accurate to 6 decimal places.)	8-bytes	0001-01-01 to 9999-12-31 (precision of time portion of TIMESTAMP is dropped prior to 1600-02-28 23:59:59 and after 7911-01-01 00:00:00)

For more information on SQL Anywhere date and time data types see "Date and time data types".

Sending and retrieving date values

Date values are stored within SQL Anywhere as either a DATE or TIMESTAMP data type, but they are passed to and retrieved from SQL Anywhere using either of three methods:

As a string, using any SQL Anywhere programming interface.
As a TIMESTAMP structure using ODBC.
As a SQLDATETIME structure using Embedded SQL.

A string containing a date value is considered unambiguous and is automatically converted to a DATE or TIMESTAMP data type without potential for misinterpretation if it is passed using the following format: yyyy-mm-dd (the "-" dash separator is one of several characters that are permitted).

Date formats other than yyyy-mm-dd can be used by setting the DATE_FORMAT database option (see "SET OPTION statement").

For more information on unambiguous date formats, see "Unambiguous dates and times".

For more information on the ODBC TIMESTAMP structure see the Microsoft Open Database Connectivity SDK, or "Sending dates and times to the database".

Used in the development of C programs, an embedded SQL SQLDATETIME structure's year value is a 16-bit signed integer.

For more information on the SQLDATETIME data type, see "Embedded SQL interface data types".

Leap years

The year 2000 is also a leap year, with an additional day in the month of February. 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 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 Feb 29, 2000 --- a leap year --- as a date and using this date determines the day of the week on which that date occurs.

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 Feb 29 does not exist in the year 2001, which it does not.

Ambiguous string to date conversions

SQL Anywhere automatically converts a string into a date when a date value is expected, even if the year is represented in the string by only two digits.

If the century portion of a year value is omitted, SQL Anywhere's method of conversion is determined by the NEAREST_CENTURY database option.

The NEAREST_CENTURY database option is a numeric value that acts as a break point between 1900 date values and 2000 date values.

Two digit years less than the NEAREST_CENTURY value are converted to 20yy, while years greater than or equal to the value are converted to 19yy.

If this option is not set, the default setting of 0 is assumed, thus adding 1900 to two digit year strings and placing them in the 20th century.

This NEAREST_CENTURY option was introduced in SQL Anywhere Release 5.5.

Ambiguous date conversion example

The following statement creates a table that can be used to illustrate the conversion of ambiguous date information in SQL Anywhere.

     CREATE TABLE T1 (C1 DATE);

The table T1 contains one column, C1, of the type DATE.

The following statement inserts a date value into the column C1. SQL Anywhere automatically converts a string that contains an ambiguous year value, one with two digits representing the year but nothing to indicate the century.

     INSERT INTO T1 VALUES('00-01-01');

By default, the NEAREST_CENTURY option is set to 0, thus SQL Anywhere converts the string into the date 1900-01-01. The following statement verifies the result of this insert.

     SELECT * FROM T1;

Changing the NEAREST_CENTURY option using the following statement alters the conversion process.

     SET OPTION NEAREST_CENTURY = 25;

When NEAREST_CENTURY option is set to 25, executing the previous insert using the same statement will create a different date value:

     INSERT INTO T1 VALUES('00-01-01');

The above statement now results in the insertion of the date 2000-01-01. Use the following statement to verify the results.

     SELECT * FROM T1;

Date to string conversions

SQL Anywhere provides several functions for converting SQL Anywhere date and time values into a wide variety of strings and other expressions. It is possible in converting a date value into a string to reduce the year portion into a two digit number representing the year, thereby losing the century portion of the date.

Wrong century values

Consider the following statement, which incorrectly converts a string representing the date Jan 1, 2000 into a string representing the date Jan 1, 1900 even though no database error occurs.

     SELECT DATEFORMAT (
                 DATEFORMAT('2000-01-01', 'Mmm dd/yy' ),
                 'yyyy-Mmm-dd' )
                 AS Wrong_year;

Although the unambiguous date string 2000-01-01 is automatically and correctly converted by SQL Anywhere into a date value, the 'Mmm dd/yy' formatting of the inner, or nested DATEFORMAT function drops the century portion of the date when it is converted back to a string and passed to the outer DATEFORMAT function.

Because the database option NEAREST_CENTURY, in this case, is set to 0 the outer DATEFORMAT function converts the string representing a date with a two digit year value into a year in the 20th century.

For more information about ambiguous string conversions, see "Ambiguous string to date conversions".

For more information on date and time functions, see "Date and time functions".