How to Format Dates in Oracle

How do I format dates in Oracle SQL?

Convert DATE or TIMESTAMP columns to human-readable text with TO_CHAR and format models.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why use TO_CHAR for date formatting in Oracle?

TO_CHAR converts DATE or TIMESTAMP columns to formatted strings, letting you control separators, month names, time-zones, and locale. Reports, CSV exports, and APIs read these strings easily.

What are the most common date format models?

Oracle format models such as 'YYYY-MM-DD', 'DD-MON-YYYY', 'HH24:MI:SS', and 'YYYY-MM-DD"T"HH24:MI:SS"Z"' cover ISO, US, and RFC patterns. Combine elements to suit any specification.

How to format order dates as ISO 8601?

SELECT TO_CHAR(order_date, 'YYYY-MM-DD') AS iso_date FROM Orders;

How to add time-zone information?

SELECT TO_CHAR(order_date_at, 'YYYY-MM-DD"T"HH24:MI:SS TZH:TZM') AS order_ts FROM Orders;

TZH and TZM append hours and minutes of the time-zone offset.

How to set a session default date format?

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';

The new setting applies to all implicit DATE-to-string conversions until the session ends.

How to include milliseconds?

SELECT TO_CHAR(order_date, 'YYYY-MM-DD HH24:MI:SS.FF3') FROM Orders;

FF1–FF9 print 1–9 fractional-second digits.

Best practices for production code?

Use explicit TO_CHAR in views, APIs, and reports. Avoid relying on client NLS settings. Document format models and test edge cases such as leap years and daylight-saving shifts.

Why How to Format Dates in Oracle is important

How to Format Dates in Oracle Example Usage


-- Return customer orders in ISO 8601 with total amount
SELECT c.name,
       TO_CHAR(o.order_date, 'YYYY-MM-DD')   AS order_date,
       o.total_amount
FROM Customers c
JOIN Orders    o ON o.customer_id = c.id
ORDER BY o.order_date DESC;

How to Format Dates in Oracle Syntax


TO_CHAR(date_expression | timestamp_expression,
       'format_model' [, 'nls_parameter'])

ALTER SESSION SET NLS_DATE_FORMAT = 'format_model';

-- Example patterns
'YYYY-MM-DD'             -- 2023-10-12
'HH24:MI:SS'             -- 14:07:59
'YYYY-MM-DD"T"HH24:MI:SS' -- 2023-10-12T14:07:59
'YYYY-Q'                 -- 2023-4 (quarter)

-- Ecommerce context
SELECT TO_CHAR(Orders.order_date, 'DD-MON-YYYY') AS order_date_txt
FROM Orders;

Common Mistakes

Relying on NLS_DATE_FORMAT: Developers assume the client’s default format matches their needs, causing inconsistent output. Fix by always calling TO_CHAR with an explicit format model.
Using two-digit years (YY): This risks Y2K-like ambiguity. Fix by using four-digit year format 'YYYY' or 'RRRR' to preserve century information.

Frequently Asked Questions (FAQs)

Can I format numbers and dates in a single call?

Yes. TO_CHAR handles both data types. Use separate format models inside the same SELECT list.

How do I localize month names?

Include NLS_DATE_LANGUAGE in the third parameter: TO_CHAR(order_date,'DD Month','NLS_DATE_LANGUAGE=ITALIAN').

Does TO_CHAR affect index usage?

Formatting occurs after data retrieval, so indexes on date columns are still used. Applying TO_CHAR in predicates, however, prevents index usage; compare raw dates instead.