How to Cast String to Date DD/MM/YYYY in PostgreSQL
-
Cast String to Date
DD/MM/YYYY
in PostgreSQL -
Use
to_char
to Format Date When Executing a Query 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 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