I have a VARCHAR data type in a order_date column which contains dates, I would like to convert it to DATE format except for null values. How can I solve this?
3 Answers
Where is an problem?
(2022-09-24 17:48:11) postgres=# select to_date('2022-07-08', 'YYYY-MM-DD');
┌────────────┐
│ to_date │
╞════════════╡
│ 2022-07-08 │
└────────────┘
(1 row)
(2022-09-24 17:48:42) postgres=# select to_date(null, 'YYYY-MM-DD');
┌─────────┐
│ to_date │
╞═════════╡
│ ∅ │
└─────────┘
(1 row)
You need use
TO_DATE(column_name,'YYYYMMDD')
Specify the format of the date in the varchar field the way it is as second input parameter to the TO_DATE function
and
apply filter
where column_name is not null
You can refer to this documentation for further reference.
The PostgreSQL formatting functions provide a powerful set of tools for converting various data types.
You should use them!
Here is the link to the related doc: https://www.postgresql.org/docs/current/functions-formatting.html
The above depends on your Postgres version. However, to_date(text, text) must work.
Related