How to convert string to date in Oracle

Galaxy Glossary

How do I convert a string to a date in Oracle SQL?

TO_DATE converts a character string to a DATE value using a specified format model.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

Why use TO_DATE instead of implicit conversion?

Explicitly calling TO_DATE guarantees Oracle interprets the string exactly as you intend, avoiding NLS-dependent surprises and making code portable across sessions.

What is the basic TO_DATE syntax?

Call TO_DATE(char_value, format_model [, nls_param]) to return a DATE. The format model tells Oracle how to parse each part of the string.

How do I convert ISO-8601 strings?

Use the format mask 'YYYY-MM-DD'.Example: SELECT TO_DATE('2024-02-29','YYYY-MM-DD') FROM dual; returns 29-FEB-24.

How can I populate ecommerce tables with dates?

When inserting into Orders, wrap date strings in TO_DATE: INSERT INTO Orders(id,customer_id,order_date,total_amount) VALUES(101,3,TO_DATE('2023-07-15 14:25','YYYY-MM-DD HH24:MI'),199.99);

How do I handle time components?

Add HH24, MI, SS, and optionally FF for fractions.Example: TO_DATE('2023-07-15 14:25:33','YYYY-MM-DD HH24:MI:SS').

What are best practices?

Always specify a format mask, store dates in DATE or TIMESTAMP columns, and validate strings before conversion to avoid runtime errors.

Which mistakes should I avoid?

A common error is mismatching the string with the mask, e.g., using 'MM' for minutes. Also, relying on session NLS settings causes inconsistent results.

Can TO_DATE parse textual month names?

Yes—use 'DD-MON-YYYY' or 'DD Month YYYY'.Example: TO_DATE('15 JUL 2023','DD MON YYYY').

How do I convert strings in bulk?

Use UPDATE with TO_DATE on each row or an external table with a DATE column and SQL*Loader date mask.

Does TO_DATE support time zones?

TO_DATE itself does not; use TO_TIMESTAMP_TZ for time-zone aware conversions, then cast to DATE if needed.

Why How to convert string to date in Oracle is important

How to convert string to date in Oracle Example Usage


-- Find all orders placed after 1-July-2023 by converting a parameter string
SELECT o.id, c.name, o.total_amount
FROM Orders o
JOIN Customers c ON c.id = o.customer_id
WHERE o.order_date >= TO_DATE('2023-07-01','YYYY-MM-DD')
ORDER BY o.order_date;

How to convert string to date in Oracle Syntax


TO_DATE(char_value, 'format_model' [, 'nls_param'])

-- Examples in ecommerce context
-- 1. Basic ISO date
SELECT TO_DATE('2023-11-05','YYYY-MM-DD') AS order_dt;

-- 2. Full timestamp during insert
INSERT INTO Orders (id, customer_id, order_date, total_amount)
VALUES (102, 4,
        TO_DATE('2023-11-05 16:45:00','YYYY-MM-DD HH24:MI:SS'),
        349.50);

-- 3. Using textual month
UPDATE Orders
SET order_date = TO_DATE('05 November 2023','DD Month YYYY')
WHERE id = 102;

Common Mistakes

Mismatch between string and format mask – e.g., '2023/07/15' with 'YYYY-MM-DD'. Fix by aligning separators and tokens exactly.
Relying on session NLS_DATE_FORMAT so TO_DATE isn't used. Queries work on one machine but fail elsewhere. Always specify TO_DATE with a mask.

Frequently Asked Questions (FAQs)

Can I omit the format model?

Yes, but Oracle then uses NLS_DATE_FORMAT, which varies by session and leads to errors. Always provide the mask.

Does TO_DATE validate dates?

Oracle raises ORA-01830/01861 if the date is invalid or the string doesn’t match the mask, preventing bad data insertion.

How do I convert strings with milliseconds?

Use TO_TIMESTAMP with 'FF' for fractions, then cast to DATE if needed: CAST(TO_TIMESTAMP('2023-07-15 14:25:33.123','YYYY-MM-DD HH24:MI:SS.FF3') AS DATE).