How to Convert a String to a Date in Oracle
Last updated August 25, 2026 · By the SaturnSQL team
Use TO_DATE with an explicit format: TO_DATE('2026-08-25', 'YYYY-MM-DD'). Never rely on implicit conversion, which depends on session NLS_DATE_FORMAT.
SELECT TO_DATE('2026-08-25', 'YYYY-MM-DD') AS hire_date FROM dual;
SELECT TO_DATE('25-AUG-2026', 'DD-MON-YYYY') AS hire_date FROM dual;Why implicit conversion breaks in production
WHERE hire_date = '2026-08-25' compiles because Oracle implicitly wraps the string in TO_DATE using the session's NLS_DATE_FORMAT. That parameter is set per client (sqlplus, a BI tool, a JDBC driver's default) and can differ from what you tested with, so the same query silently parses dates differently, or throws ORA-01858 or ORA-01861, depending on who runs it. Always call TO_DATE explicitly with a fixed format model so the conversion does not depend on session state.
-- fragile: depends on NLS_DATE_FORMAT of whoever runs it
SELECT * FROM orders WHERE order_date = '25-08-2026';
-- explicit and portable
SELECT * FROM orders WHERE order_date = TO_DATE('2026-08-25', 'YYYY-MM-DD');Exact matching with FX
By default TO_DATE tolerates extra whitespace and flexible punctuation between format elements. Prefix the format model with FX to require an exact match, character for character, which catches malformed input instead of silently accepting it.
SELECT TO_DATE('2026-08-25', 'FXYYYY-MM-DD') FROM dual; -- fails loudly on '2026-8-25'A DATE column always stores both date and time components, down to the second, even if you only ever populate the date part. TO_DATE('2026-08-25', 'YYYY-MM-DD') produces midnight, so comparisons against a column that has non-zero times (e.g. order_date > TO_DATE('2026-08-25','YYYY-MM-DD')) will include rows from later that same day unless you also TRUNC the column or bound the range explicitly.
Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free