Back to cookbooks list Articles Cookbook

How to Format a Date in Oracle

  • TO_CHAR()

Problem:

You want to format a date column or value in Oracle.

Example:

An online store’s database has a table named order with data in the columns order_id, customer_id, and order_time.

order_idcustomer_idorder_time
BKN234GH748230472022-12-01 01:43:44
F7N3JK84J845638472022-12-02 08:07:12
HSTHJLL2P539403342022-12-15 15:26:02

We want to extract the info for each order, but we want to know only the date of the order, ignoring an exact time. Also, we want to have the date in the format 'DD/MM/YYYY' (for example: 03/12/2022).

Download SQL for Data Analysis Cheat Sheet

Solution:

Here’s how you would solve this problem:

SELECT 
  order_id, 
  customer_id, 
  to_char(order_time, 'DD/MM/YYYY') as order_date 
FROM order;

And here’s the result you would get:

order_idcustomer_idorder_date
BKN234GH7482304701/12/2022
F7N3JK84J8456384702/12/2022
HSTHJLL2P5394033415/12/2022

Discussion:

To format a date (or timestamp) in Oracle, use the function to_char(). This function takes a date, formats it to your definition, and returns a string. It requires two parameters: a date value and a format. The format is a string (text) pattern specifying what the date should look like. It contains placeholders for date elements (e.g. YYYY is the placeholder for a 4-digit year) and the punctuation to be used in the date format.

Some popular examples of date formats are:

  • YYYY-MM-DD
    • YYYY - year (4 digits)
    • MM - month number (01–12)
    • DD - day of month (01–31)
    • Elements are separated by a hyphen (-)
    • Sample output: '2022-03-24'
  • DD/MM/YYYY
    • YYYY - year (4 digits)
    • MM - month number (01–12)
    • DD - day of month (01–31)
    • Elements are separated by a slash (/)
    • Sample output: '24/03/2022'
  • Month DD, YYYY
    • Month - Capitalized full month name (e.g. ‘February’ or ‘May’)
    • The month and day of the month are separated from the year by a comma
    • Sample output: 'March 24, 2022'
  • DD-MON-YY
    • MON - Abbreviated upper case month name (e.g. ‘FEB’ for February or ‘DEC’ for December)
    • YY - Last 2 digits of year
    • Sample output: '24-MAR-22'

All possible format options can be found in the official Oracle documentation.

Recommended courses:

Recommended articles:

See also: