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