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.
I’m on iPhone 13 iOS 16. I’m not seeing a search bar. Is that intentional?