Year 2000 compliance

Reference Manual
CHAPTER 7. SQL 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 Adaptive Server Anywhere. It illustrates how date values are handled internally by Adaptive Server Anywhere, and how Adaptive Server Anywhere handles ambiguous date information, such as the conversion of a two digit year string value.

Users of Sybase Adaptive Server 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 Adaptive Server Anywhere year 2000 compliance:

Adaptive Server 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, the Adaptive Server Anywhere internal storage of dates explicitly includes the century portion of a year value.
The operation of Adaptive Server Anywhere is unaffected by any return value, including the current date.
Date values can always be output 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.

How dates are stored

Dates containing year values are used internally and stored in Adaptive Server 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 the 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 the time portion of TIMESTAMP is dropped prior to 1600-02-28 23:59:59 and after 7911-01-01 00:00:00)

For Info For more information on Adaptive Server Anywhere date and time data types see Date and time data types.

Sending and retrieving date values

Date values are stored within Adaptive Server Anywhere as either a DATE or TIMESTAMP data type, but they are passed to and retrieved Adaptive Server Anywhere using one of the following methods:

As a string, using any Adaptive Server 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 Info For more information on unambiguous date formats, see Unambiguous dates and times.

For Info 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 Info For more information on the SQLDATETIME data type, see Embedded SQL data types.

Leap years

The year 2000 is also a leap year, with an additional day in the month of February. Adaptive Server 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.

Adaptive Server Anywhere handles all leap years correctly. For example:

The following SQL statement results in a return value of "Tuesday":

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

Adaptive Server Anywhere accepts Feb 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 Adaptive Server 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.

Ambiguous string to date conversions

Adaptive Server 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, Adaptive Server 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 19YY date values and 20YY 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 Adaptive Server 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 Adaptive Server 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. Adaptive Server 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 Adaptive Server 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

Adaptive Server Anywhere provides several functions for converting Adaptive Server 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 Adaptive Server 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 Info For more information on date and time functions, see Date and time functions.

For Info