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.
The Oracle TO_DATE() function converts a date literal to a DATE value.
The TO_DATE function accepts an argument of a character data type and converts this value to a DATETIME value. The TO_DATE function evaluates a character string according to the date-formatting directive that you specify and returns a DATETIME value.
To_char formats a DATE into a string using the given format mask. To_date converts a STRING into a date using the format mask. Your client then displays that date using a format mask (set at session/instance level).
Date functions in Oracle can be defined as a set of functions which operate on date and allows the developer or users to retrieve the current date and time in a particular time zone or extract only the date/ month/year or more complex actions like extracting the last day of the month/ next day/ session time zone and it ...
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:
https://asktom.oracle.com/pls/apex/f?p=100:11:::NO:RP:P11_QUESTION_ID:9522299800346947976
http://www.oracledba.co.uk/tips/dates_8i.htm
You can use the 'FX' format string to make it match exactly:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements004.htm#r18c1-t65
http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements004.htm#SQLRF00216
You learn something new every day! Thanks for asking this!
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With