In order to convert datetime(timestamp) to date in postgresql. We will be using CAST() function Or ::(CAST Notation). We can convert a timestamp (datetime) to date by using the CAST() or ::date syntax. Lets see an example of both approaches. We will also consider an example on how to convert datetime column to date column in postgresql.
Convert datetime(timestamp) to date using CAST() function Postgresql:
We will be passing datetime into cast function which will convert datetime to date in postgresql
SELECT CAST('2024-02-24 15:30:45.791354' AS DATE);
Converting datetime column to date column using cast() function :
We will be using below Student_detail table for our example to depict on how to convert datetime column to date column in postgresql.
Student_detail:
We will be passing column that needs to be converted into cast() function, which will convert datetime to date in postgresql
select *, CAST(birthdaytime AS DATE) as birthday_converted_date from student_detail
“birthdaytime” (datetime) column is typecasted to date column named “birthday_converted_date”.
Convert datetime to date using CAST Notation (::) Postgresql
By using cast notation first we need to convert to timestamp and then to date which is mentioned in the example below.
select '2024-02-24 15:30:45'::timestamp::date
Converting datetime column to date column using cast notation (::) :
We will be using below Student_detail table for our example to depict on how to convert datetime column to date column in postgresql.
Student_detail:
We will be using cast notation (::) along with the column that needs to be converted from datetime to date in postgresql.
SELECT *,birthdaytime::timestamp::date AS birthday_converted_date FROM student_detail;
“birthdaytime” (datetime) column is typecasted to date column named “birthday_converted_date”.
