im trying to insert select a casted timestamp in a date colum timestamp like 02-09-2021 00:00:00, and i need to convert this timestamp to date dd-mm-yyyy without hhmmss, i’ve tried select date(column) as newdate but when i check my table insert, it keeps like timestamp, all the solutions that i tried only runs perfectly only in a select sentence, but when i try to insert select.. i keep with timestamp type..
Advertisement
Answer
If you need to convert formatted text to timestamp then use to_timestamp with explicit format specification. Try this:
select to_timestamp('02-09-2021 00:00:00', 'dd-mm-yyyy hh24:mi:ss');
| to_timestamp |
|---|
| 2021-02-09 00:00:00.000 +0200 |
To convert it to date:
select to_date('02-09-2021 00:00:00', 'dd-mm-yyyy');
If you need to change the type of an existing timestamp column to date then:
alter table the_table alter column the_column type date using the_column::date;