converting string to date using TO_DATE for MM/DD/YY gives wrong values for dates before 1970 [snowflake-cloud-data-platform]

Question

Trying to convert a date string 03/31/69 to 1969-03-31 standard date format using TO_DATE function.

SELECT TO_DATE('03/31/69', 'MM/DD/YY');

Expected value was 1969-03-31 but I am getting 2069-03-31.

please let me know how to handle the century correctly for MM/DD/YY formats in snowflake.

This works fine for dates after 1970. It returns the correct century information. Only for dates before 1970 its converting to 21st century. Please let me know if this is a known behavior. If so how to convert such formats to standard date format?

Answer 1

In Snowflake, how YY is interpreted depends on the TWO_DIGIT_CENTURY_START session parameter, which defaults to 1970.

To get the result that you want for your input data, you would need to alter the parameter to a value that is not greater than 1969:

ALTER SESSION SET TWO_DIGIT_CENTURY_START = 1950;
SELECT TO_DATE('03/31/69', 'MM/DD/YY');