VARCHAR to DATE format in Postgresql-CodePudding

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?

CodePudding user response：

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)

CodePudding user response：

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.

CodePudding user response：

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.