Monday, 17 September 2007

Oracle to_date function dates from 20th century

there is a very useful function in Oracle DB to_date. But when you run:
select to_date('78/06/01','YY/MM/DD') from dual;

you might be expecting a date '1-Jun-1978' (might differ depending on your local nls settings), but you get surprisingly: '1-Jun-2078'.
Helpful is parameter RR instead of YY.
RR returns years from 21st century when given value is in range 0-49
and returns years from 20th century when given value is in range 50-99

For most cases you probably want:
select to_date('78/06/01','RR/MM/DD') from dual;

thanks
chris

No comments: