Skip to Main Content
  • Questions

  • TO_DATE function works even when format doesn't match the string, why?

Breadcrumb

Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions

Question and Answer

Connor McDonald

Thanks for the question, Paul.

Asked: September 23, 2015 - 3:42 am UTC

Last updated: July 17, 2020 - 7:33 am UTC

Version: 11.2.0.4

Viewed 50K+ times! This question is

You Asked

I'm always using proper format for to_date function. However I found that to_date works on even when format doesn;t match the string. Please see below:


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option


SQL> show parameter nls_date

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_date_format string DD-MON-RR
nls_date_language string AMERICAN


SQL> create table testtest (d date);
Table created.

-- Case #1, correct format
SQL> insert into testtest values (to_date('2015-09-22 23:19:00','YYYY-MM-DD HH24:MI:SS'));

1 row created.

-- Case #2, two dashes removed
SQL> insert into testtest values (to_date('20150923 23:20:00','YYYY-MM-DD HH24:MI:SS'));

1 row created.

-- Case #3, all dashes, spaces, colons removed
SQL> insert into testtest values (to_date('20150924233000','YYYY-MM-DD HH24:MI:SS'));

1 row created.

-- Case #4, only date portion in YYYYMMDD format remains
SQL> insert into testtest values (to_date('20150925','YYYY-MM-DD HH24:MI:SS'));

1 row created.

SQL> select to_char(d,'YYYY-MM-DD HH24:MI:SS') from testtest;

TO_CHAR(D,'YYYY-MM-
-------------------
2015-09-22 23:19:00
2015-09-23 23:20:00
2015-09-24 23:30:00
2015-09-25 00:00:00

4 rows selected.


I was expecting to get errors in cases 2-4, but all statements work ok, why?

and Connor said...

Oracle will try a number of mechanisms to try work out a date for you , eg

SQL> select to_date('01JAN2000','ddmmyyyy') from dual;

TO_DATE('
---------
01-JAN-00

SQL> select to_date('01JAN1999','ddmmyyyy') from dual;

TO_DATE('
---------
01-JAN-99

SQL> select to_date('01JAN2000','dd/mm/yy') from dual;

TO_DATE('
---------
01-JAN-00

SQL>  select to_date('01JAN2000','dd-mm-yyyy') from dual;

TO_DATE('
---------
01-JAN-00

SQL>


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Enforce format

Marcus, September 23, 2015 - 6:40 am UTC

You can force Oracle to check the exact format if you use 'FX...'

select to_date('20150923 23:20:00','FXYYYY-MM-DD HH24:MI:SS') x from dual;
-> ORA 01861 - "literal does not match format string"

select to_date('20150923 23:20:00','FXYYYYMMDD HH24:MI:SS') x from dual;
-> 23.09.2015

http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements004.htm#r18c1-t65
Connor McDonald
September 24, 2015 - 1:37 am UTC

Thanks for contributing extra information.

Unfortunately the link no longer works

Matej, July 16, 2020 - 8:56 am UTC

Unfortunately the link no longer works
Connor McDonald
July 17, 2020 - 7:33 am UTC

Content updated.

Thanks for letting us know.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library