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.

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 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;

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 will create 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;