Handling of two-digit years

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, the 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 50 is assumed. So, two-digit year strings are understood to refer to years between 1950 and 2049.

This nearest_century option was introduced in SQL Anywhere Version 5.5. In version 5.5, the default setting was 0.

Example

The following statement creates a table that can be used to illustrate the conversion of ambiguous date information in SQL Anywhere (Note that the date order is assumed to be YMD):

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:

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

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. By default, the nearest_century option is set to 50, so SQL Anywhere converts the above string into the date 2000-01-01. The following statement verifies the result of this insert:

SELECT * FROM T1;

To change the default behavior for handling the year value when it doesn't contain the century, you can change the nearest_century option. For example:

SET OPTION nearest_century = 0;

Now, when you execute the INSERT statement again, the date inserted is 1900-01-01.

Discuss this page in DocCommentXchange.