Tuesday 15 March 2011

Date format tolerance

This is just a little ditty to remind me which date format mask is more tolerant.
SQL> select to_date('21-03-2011','dd-mon-yyyy') from dual;
select to_date('21-03-2011','dd-mon-yyyy') from dual
               *
ERROR at line 1:
ORA-01843: not a valid month

SQL> select to_date('21-mar-2011','dd-mm-yyyy') birthday from dual;

BIRTHDAY
-------------------
21-03-2011 00:00:00

1 row selected.
Note that it accepts 'MAR' when using the MM format, but not numerics when using the MON format.

You may like to consider this when defining an application wide default format.
Shared Components -> Globalization Attributes
Scott

2 comments:

Connor McDonald said...

yeah, but be careful with timestamps...

select ...
from ...
where timestamp_col > '01-jan-10'

will work as expected, but

'01-jan-2010'

will be treated as '01-jan-20'

Scott Wesley said...

Maybe it's the Friday afternoon before Bali, but I'm not sure I get where you're going with that?