Year 2000 compliance

Description

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 Sybase IQ. It illustrates how Sybase IQ handles date values internally, and how it handles ambiguous date information such as the conversion of a 2-digit year string value.

Consider the following measurements of Sybase IQ year 2000 compliance:

• It 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 internal storage of dates explicitly includes the century portion of a year value.

• The operation 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.

Date values are stored within Sybase IQ as either a DATE or TIMESTAMP data type. Time values are stored as a TIME or TIMESTAMP data type. They are passed to and retrieved from it using either of three methods:

• As a string, using any IQ 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).

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

Sybase IQ handles all leap years correctly. For example:

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

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

It 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:

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.

Sybase IQ 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, the conversion method 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 50 is assumed (0 to 49 are in the 21st century, 50 to 99 are in the 20th century).

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

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. It 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 50, thus Sybase IQ converts the string into the date 2000-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 = 0; 

When NEAREST_CENTURY option is set to 0, executing the previous insert using the same statement creates a different date value:

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

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

SELECT * FROM T1;

Sybase IQ provides several functions for converting 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.

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

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

Although the unambiguous date string 1900-01-01 is automatically and correctly converted by Sybase IQ 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 50 the outer DATEFORMAT function converts the string representing a date with a two digit year value into a year in the 21st century.