How to Format a Date in PostgreSQL

In this guide, you’ll learn how to format DATE values into many different formats, how to insert DATE values into a table, and more.

Summary

You can insert date values into a DATE column in Postgres in many different formats, but the recommended format is the ISO format of YYYY-MM-DD.

You can format dates in Postgres when you display them using the TO_CHAR function, such as TO_CHAR(order_date, ‘MM-DD-YYYY’).

 

Postgres Date Data Type

We’ll be working with the DATE data type in this guide.

The DATE data type in Postgres captures the date without the time component.

There is a range of values it can store: from 4,713 BC to 5,874,897 AD.

To define a column with the DATE data type, simply specify the data type:

CREATE TABLE cust_order (
  order_id INT,
  order_date DATE
);

The order_date column is stored as a DATE value.

Let’s insert some data into this table.

You can find all of the SQL scripts used in this guide in my GitHub repository.

 

Insert a Date into a Table

In many other databases, you have to insert date values using a certain default format, or use a function to convert your input format into the required format.

However, in Postgres, you can insert date data in many different types, and it will work.

The recommended format is the one that meets the ISO 8601 standard, which is YYYY-MM-DD.

Here are a few examples of inserting data into a DATE column in Postgres.

INSERT INTO cust_order (order_id, order_date) VALUES (1, '2022-10-13');
INSERT INTO cust_order (order_id, order_date) VALUES (2, '14-OCT-2022');
INSERT INTO cust_order (order_id, order_date) VALUES (3, '20221015');

All three of these rows should be inserted without issues.

However, we can try to run this statement:

INSERT INTO cust_order (order_id, order_date) VALUES (4, '16-10-2022');

We’ll get an error:

ERROR:  date/time field value out of range: "16-10-2022"
LINE 1: ...INTO cust_order (order_id, order_date) VALUES (4, '16-10-202...
                                                             ^
HINT:  Perhaps you need a different "datestyle" setting.
SQL state: 22008
Character: 58

This happens because the format we have specified is not clear to the database which field is the day and which is the month. We know by looking at it that 16 is the day because there is no month number of 16.

However, the database doesn’t know this.

A way around this, as mentioned by the hint in the error message, is to use a different DateStyle setting. You can find out more in the Postgres documentation here.

We can select from this table and see the results.

SELECT order_id, order_date
FROM cust_order;

Results:

order_id	order_date
1	2022-10-13
2	2022-10-14
3	2022-10-15

The three rows that were inserted are shown here.

 

Display a Date in a Certain Format Using TO_CHAR

When we select a DATE column from our table, we can see the format that is shown:

SELECT order_id, order_date
FROM cust_order;

Results:

order_id	order_date
1	2022-10-13
2	2022-10-14
3	2022-10-15

The format of the date value is YYYY-MM-DD.

What if we want to show it in a different format?

You can use the TO_CHAR function in Postgres.

The TO_CHAR function looks like this:

TO_CHAR (date_value, output_format)

The parameters are:

• date_value: the value you want to display in a different format.
• output_format: the format to display

The output_format must be in single quotes and uses the patterns in the table below.

For example, a value in your function of YYYY will be replaced with the four-digit year when it is displayed.

This is the list of output format values that apply to dates.

Pattern	Description
Y,YYY	year (4 or more digits) with a comma
YYYY	year (4 or more digits)
YYY	last 3 digits of year
YY	last 2 digits of year
Y	last digit of year
IYYY	ISO 8601 week-numbering year
IYY	last 3 digits of ISO 8601 week-numbering year
IY	last 2 digits of ISO 8601 week-numbering year
I	last digit of ISO 8601 week-numbering year
BC, bc, AD, or ad	era indicator without periods
B.C., b.c., A.D. or a.d.	era indicator with periods
MONTH	full upper case month name (blank-padded to 9 characters)
Month	full capitalised month name (blank-padded to 9 characters)
month	full lower case month name (blank-padded to 9 characters)
MON	abbreviated upper case month name
Mon	abbreviated capitalised month name
mon	abbreviated lower case month name
MM	month number (01-12)
DAY	full upper case day name (blank-padded to 9 characters)
Day	full capitalised day name (blank-padded to 9 characters)
day	full lower case day name (blank-padded to 9 characters)
DY	abbreviated upper case day name (blank-padded to 9 characters)
Dy	abbreviated capitalised day name (blank-padded to 9 characters)
dy	abbreviated lower case day name (blank-padded to 9 characters)
DDD	day of year (001-366)
IDDD	day of ISO 8601 week-numbering year (001-371). Day 1 of the year is Monday of the first ISO week
DD	day of month (01-31)
D	day of the week, Sunday (1) to Saturday (7)
ID	ISO 8601 day of the week, Monday (1) to Sunday (7)
W	week of the month (1-5). The first week starts on the first day of the month
WW	week number of the year (1-53). The first week starts on the first day of the year
IW	week number of ISO 8601 week-numbering year (01-53). The first Thursday of the year is in week 1.
CC	2 digit century (the 21st century starts on 2001-01-01)
J	Julian Date (number of days since Nov 24, 4714 BC)
Q	quarter
RM	month in upper case Roman numerals (I-XII, I=January)
rm	month in lower case Roman numerals (i-xii, i=January)

Let’s see some examples.

 

Example 1 – DD/MM/YYYY

Here’s an example of formatting dates in the DD/MM/YYYY format.

SELECT
order_date, 
TO_CHAR(order_date, 'DD/MM/YYYY') AS formatted_date
FROM cust_order;

Results:

order_date	formatted_date
2022-10-13	13/10/2022
2022-10-14	14/10/2022
2022-10-15	15/10/2022

 

Example 2 – Names

Here’s an example using the names of days and months. We’ve also added two examples of the same part of the date: the Day (name of the day) and DD (day number).

SELECT
order_date, 
TO_CHAR(order_date, 'Day, DD Month, YYYY') AS formatted_date
FROM cust_order;

Results:

order_date	formatted_date
2022-10-13	Thursday, 13 October, 2022
2022-10-14	Friday, 14 October, 2022
2022-10-15	Saturday, 15 October, 2022

We can see the name of the day, the date, the month name, and the year.

 

Example 3 – Other Attributes

Here’s an example of getting other attributes of the date.

SELECT
order_date, 
TO_CHAR(order_date, 'MM') AS month_num,
TO_CHAR(order_date, 'DDD') AS day_of_year,
TO_CHAR(order_date, 'ID') AS day_of_week,
TO_CHAR(order_date, 'WW') AS week_of_year,
TO_CHAR(order_date, 'J') AS julian_date
FROM cust_order;

Results:

order_date	month _num	day_of _year	day_of _week	week_of _year	julian _date
2022-10-13	10	286	4	41	2459866
2022-10-14	10	287	5	41	2459867
2022-10-15	10	288	6	42	2459868

We can see a few different attributes of the date values here.

 

Frequently Asked Questions

 

How do I convert a date to a string in PostgreSQL?

You can use the TO_CHAR function to convert a date value to a string. The output of this function is a string.

As shown in the examples above, you can define the output format however you like.

 

How do I convert a datetime to a date in PostgreSQL?

To convert a DATETIME to a DATE (or remove the time component), you can either use the DATE function or the ::DATE operator.

You can enclose your DATETIME within the DATE function. Or, you can add ::DATE after the DATETIME value.

Here’s an example using the NOW() function, which returns the current date and time in a DATETIME format.

SELECT
NOW(),
NOW()::DATE,
DATE(NOW());

Results:

now	now	date
2022-10-13 18:28:10.356797+11	2022-10-13	2022-10-13

We can see in the second and third columns that the time component is removed.

 

Conclusion

Formatting a date in Postgres can be done using the TO_CHAR function which is quite flexible. You can also insert date values using a range of formats, but the recommended format is the ISO format of YYYY-MM-DD.