How to Format a Date in Oracle

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.

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

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:

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.