Hi Tom,

Can you explain to my these behaviour below.
I want to check if the source data has a valid DATE format fitting exactly to the given format mask.
If the given string (to be converted to DATE) has not the appropriated given format mask Oracle shout not try to be ¿clever¿ and so it never should just perform an arbitrary conversion and look what probably could fit anyway.

Is there a way to force a absolutely stringent behaviour for the converting to square with the given format mask?

Here some simple examples:

SELECT TO_DATE ('2006.02.12', 'DD-MM-YYYY HH24:MI:SS')
FROM DUAL
--> accepted result: Exception ORA-01861
--> adduced result: 20.06.0002 12:00:00
-- ??

SELECT TO_DATE ('2006.02.12', 'DD-MM-YYYY')
FROM DUAL
--> expected result: Exception ORA-01861
--> adduced result: Exception ORA-01861
--> OK

SELECT TO_DATE ('02.12-2006', 'DD-MM-YYYY HH24:MI:SS')
FROM DUAL
--> expected result: Exception ORA-01861
--> adduced result: 2.12.2006
--> ??

SELECT TO_DATE ('02.12-2006', 'DD-MM-YYYY')
FROM DUAL
--> expected result: Exception ORA-01861
--> adduced result: 2.12.2006
--> ??

Thanks and Regards
Andre

ops$tkyte%ORA10GR2> SELECT TO_DATE ('2006.02.12', 'fxDD-MM-YYYY HH24:MI:SS')
  2  FROM DUAL
  3  /
SELECT TO_DATE ('2006.02.12', 'fxDD-MM-YYYY HH24:MI:SS')
                *
ERROR at line 1:
ORA-01861: literal does not match format string


ops$tkyte%ORA10GR2> SELECT TO_DATE ('2006.02.12 12:34:56', 'fxDD-MM-YYYY HH24:MI:SS')
  2  FROM DUAL
  3  /
SELECT TO_DATE ('2006.02.12 12:34:56', 'fxDD-MM-YYYY HH24:MI:SS')
                *
ERROR at line 1:
ORA-01861: literal does not match format string


ops$tkyte%ORA10GR2> SELECT TO_DATE ('02-12-2006 12:34:56', 'fxDD-MM-YYYY HH24:MI:SS')
  2  FROM DUAL
  3  /

TO_DATE('02-12-20061
--------------------
02-dec-2006 12:34:56