Converting varchar to date in postgresql (date_parse doesn't work)-CodePudding

Running this existing query in presto:

date(date_parse(activation_date, '%%m-%%d-%%Y')) from table1

gives the error

"Invalid format: "02/06/2022""

Activation_date is varchar, showing MM/DD/YYYY

How do I convert it to a date so that I can join it to a column that is already in postgresql date type? Thank you so much!

CodePudding user response：

The expected format is %m/%d/%Y in Trino (formerly PrestoSQL).

trino> SELECT date(date_parse('02/06/2022', '%m/%d/%Y'));
   _col0
------------
 2022-02-06

https://trino.io/docs/current/functions/datetime.html?highlight=date_parse#mysql-date-functions

CodePudding user response：

date_parse is not a Postgres function.
You don't need to escape % in Postgres strings.
Your format has - while your string has /.

If your DateStyle is set to MDY (Month Day Year), simply cast the string to a date.

# SELECT current_setting('datestyle');
 current_setting 
-----------------
 ISO, MDY

# select '02/06/2022'::date;
    date    
------------
 2022-02-06

See Date/Time Input for more.