This is an old revision of the document!


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):

Parameter Value
NLS_DATE_FORMAT DD-MON-RR
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR

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|Oracle Reference]].