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.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Thank you! Your submission has been received!

Oops! Something went wrong while submitting the form.

Description

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

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.