How to Cast String to Date DD/MM/YYYY in PostgreSQL

David Mbochi Njonge Feb 02, 2024
  1. Cast String to Date DD/MM/YYYY in PostgreSQL
  2. Use to_char to Format Date When Executing a Query in PostgreSQL
How to Cast String to Date DD/MM/YYYY in PostgreSQL

Casting is the process of converting one data type to another data type. In PostgreSQL, we cannot specify a format for a date column, but we can specify the date format we want while retrieving the data.

We can also specify the format we want in the logic of our application when not working directly on the database. This tutorial shows how to retrieve a date using a format different from when the data was created.

Cast String to Date DD/MM/YYYY in PostgreSQL

Use the command below to log in to the PostgreSQL server.

david@david-HP-ProBook-6470b:~$ psql -U postgres
Password for user postgres:

Enter the password for user postgres and press the Enter button on your keyboard.

psql (14.2 (Ubuntu 14.2-1.pgdg18.04+1))
Type "help" for help.

postgres=#

Create a database named date_db which will contain the entity having the date column. Copy and paste the following SQL command on your terminal and press the Enter button on your keyboard.

postgres=# create database date_db;
CREATE DATABASE

Connect to the date_db database to ensure that queries are executed on our created database. Use the command below to connect to the date_db database.

postgres=# \c date_db;
You are now connected to database "date_db" as user "postgres".

Create a table named image with the fields id, width, height, and created_at. The fields id, width, and height are type integer, while created_at is type date.

date_db=# create table image(id SERIAL NOT NULL, width integer, height integer, created_at date, PRIMARY KEY(id));
CREATE TABLE

Insert a record of an image instance into the image table. Copy and paste the following SQL command on your terminal and press the Enter button on your keyboard.

Note that no date format was specified during creating the column created_at.

date_db=# insert into image(width, height, created_at) values(200,400,'2022-03-29');
INSERT 0 1

We used the format YYYY-MM-DD while inserting a record into the image table.

Use to_char to Format Date When Executing a Query in PostgreSQL

The to_char is a PostgreSQL formatting function. The function can be overloaded with different data types to return, such as text or date.

Copy and paste the following SQL command on your terminal and press the Enter button on your keyboard.

date_db=# select to_char("created_at", 'DD/MM/YYYY') from image;
  to_char
------------
 29/03/2022
(1 row)

The to_char function accepts two parameters, a column containing the dates and the date format pattern to format the date during the select query.

Note that we used the format DD/MM/YYYY to retrieve our data from the database and the format YYYY-MM-DD to insert our data into the database.

Another way to change the display format is to use the DateStyle, but this is not recommended as it affects how dates are parsed in the PostgreSQL database.

David Mbochi Njonge avatar David Mbochi Njonge avatar

David is a back end developer with a major in computer science. He loves to solve problems using technology, learning new things, and making new friends. David is currently a technical writer who enjoys making hard concepts easier for other developers to understand and his work has been published on multiple sites.

LinkedIn GitHub

Related Article - PostgreSQL Date