2

I want a result 2000-02-05 with below query in snowsql.

alter session set  TWO_DIGIT_CENTURY_START=2000;
select cast ('05-FEB-00' as date) from dual;

But I am getting 0001-02-05. I am using existing script to load date in snowflake which works for oracle. I know I can get expected result using to_date function but I don't want to do so. If I have to then I have change many place in script which is hectic.

I want solution using cast function. Do anyone know what is happening here?

Dinesh Subedi
  • 2,603
  • 1
  • 26
  • 36

1 Answers1

6

You first need to specify the non-default date format for your input data. In the case of the example above:

alter session set date_input_format = 'DD-MON-YY';

Then

alter session set  TWO_DIGIT_CENTURY_START=2000;
select cast ('05-FEB-00' as date) from dual;

yields:

2000-02-05

Stuart Ozer
  • 1,354
  • 7
  • 7
  • 1
    Note, TWO_DIGIT_CENTURY_START is not strictly needed for this date, 00 will still translate to 2000 with the default setting. – Marcin Zukowski Apr 25 '18 at 17:22