Back to cookbooks list Articles Cookbook

How to Format a Date in PostgreSQL

  • TO_CHAR()

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.

first_namelast_namehire_date
AgathaSmith2021-03-15
WilliamParker2022-01-22
NoahCrawford2009-10-01

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

Download SQL for Data Analysis Cheat Sheet

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:

first_namelast_namehire_date
AgathaSmith15/03/2021
WilliamParker22/01/2022
NoahCrawford01/10/2009

Discussion:

Discover the best interactive PostgreSQL courses

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.

Recommended courses:

Recommended articles:

See also: