| TO_DATE formating [message #650189] |
Tue, 19 April 2016 03:27  |
pointers
Messages: 451
Registered: May 2008
|
Senior Member |
|
|
Hi,
Could you please help me in understanding why oracle treats the first set and second set differently.
In the first set oracle is able to match the format but not in second set.
I am aware of the fact that format matching should be done either by FX or "" as Solomon posted here.
https://community.oracle.com/thread/2412493?tstart=0
Set 1:
select to_date('20120704','YYYYMMDD') from dual ;
TO_DATE('20
-----------
04-JUL-2012
1 row selected.
SQL> select to_date('20120704','YYYY/MM/DD') from dual ;
TO_DATE('20
-----------
04-JUL-2012
1 row selected.
SQL> select to_date('20120704','YYYY-MM-DD') from dual ;
TO_DATE('20
-----------
04-JUL-2012
1 row selected.
SQL> select to_date('20120704','YYYY-----MM-----DD') from dual ;
TO_DATE('20
-----------
04-JUL-2012
1 row selected.
Set 2:
select to_date('20120704','YYYY/MM/DD') from dual ;
Is it because as Tom said -
Quote:because - and / are similar to "whitespace might be here" without the format exact specification.
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:254129800346358589
Thank you in advance.
Regards,
Pointers
|
|
|
|
|
|
| Re: TO_DATE formating [message #650191 is a reply to message #650190] |
Tue, 19 April 2016 03:55  |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
The documentation on format masks states that oracle gives flexibility if the string to be converted doesn't contain all the punctuation characters (-/,.;: <space>) in the format mask. You can skip some or all or use a different punctuation character and as long as all the numerics are present it'll still work.
It gives you no such flexibility the other way around. So if the string has more punctuation than the mask you'll get an error.
That's just what oracle decided.
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
