Oracle TO_DATE function

Question

I always thought that to_date function string and date format should match. Why is the below statement working fine?

select TO_DATE('20151014','yyyy-mm-dd') from dual;

Is oracle now ignoring the special characters before converting string to date?

This is in Oracle 11g.

Yeah, that works too.. But why is the below statement working? select TO_DATE('2015/10$14','yyyy--%-mm-dd') from dual; — spider8, Oct 22 '15 at 19:43

score 5 · Answer 1 · answered Oct 22 '15 at 20:53

5

I found this interesting and asked the Google, which told me that basically when formats don't match Oracle tries it's hardest to make it work:

You can use the 'FX' format string to make it match exactly:

You learn something new every day! Thanks for asking this!

answered Oct 22 '15 at 20:53

Gary_W

Very interesting... I always thought that Oracle was very rigid with date formatting, compared to other RDBMSs. I guess you just have to know how to push its buttons. – Hambone Oct 22 '15 at 20:56
Same here. Very interesting. – Gary_W Oct 22 '15 at 20:57
I have also noticed this when I did something like 10-14-2015 when the date format actually called for 10/14/2015. This is good to know. – McGlothlin Oct 22 '15 at 21:02

1 Answers1