DATE and TIMESTAMP Formats in Oracle

You can run the following query to determine the default format for DATE and TIMESTAMP data type values in your Oracle database:

Oracle:

   -- Read character set and length semantics parameters
   SELECT parameter, value 
   FROM nls_database_parameters
   WHERE parameter LIKE 'NLS_%_FORMAT';

Sample output (Oracle default settings):

When a string literal meets the current date format you can use TO_DATE function to convert the string value to DATE without specifying the format string:

Oracle:

  -- Use TO_DATE without the format string
  SELECT TO_DATE('21-DEC-2017') FROM dual;
  # 21-DEC-17
 
  -- This will cause an error since the format is not matched
  SELECT TO_DATE('2017-12-21') FROM dual;
  # ORA-01861: literal does not match format string
 
  -- You can to specify the format string 'YYYY-MM-DD' explicitly
  SELECT TO_DATE('2017-12-21', 'YYYY-MM-DD') FROM dual;
  # 21-DEC-17

For more information, see Oracle Reference.