How to Format a Date in PostgreSQL

Problem:

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

Example:

The company’s database has a table named employees with data in the columns first_name, last_name, and hire_date.

We want to extract the information for each employee, but we want to have hire_date in the dd/mm/yyyy format (e.g. 03/12/2022).

Solution:

Here’s how you would solve this problem:

SELECT 
  first_name, 
  last_name, 
  TO_CHAR(hire_date, ‘dd/mm/yyyy’) 
FROM employees;

And here’s the result you would get:

Discussion:

To format a date in PostgreSQL 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 forward slash (/)
  • Sample output: ‘24/03/2022’
• Month DD, YYYY
  • Month - Capitalized full month name (e.g. ‘February’ or ‘May’)
  • The month and day 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 PostgreSQL documentation.