The Postgres Query select date('22-02-2022') is showing Error message.

I need to save date in a column of a table, but the input date can be any format. Please suggest how to save date in YYYY-MM-DD format. Following is the Error screenshot:

CodePudding user response：

A date has no format at all. But if you specify a date literal (constant) the way you tried, it has to be in yyyy-mm-dd format:

select date '2022-02-22'

If you want to specify the value in a different format, use the to_date() function:

select to_date('22-02-2022', 'dd-mm-yyyyy');

If the column in the table is defined with the date data type, the way you specify the actually value is irrelevant as it will be stored as a binary value without any format.

If you need a specific format when selecting (displaying) those values, you can use the to_char() function.

CodePudding user response：

You might use TO_DATE function with date format.

SELECT TO_DATE('22-02-2022','dd-mm-yyyy');

there is another TO_CHAR function let DateTime type format as your expectation.

SELECT TO_CHAR(TO_DATE('22-02-2022','dd-mm-yyyy'),'yyyy-mm-dd')

CodePudding user response：

If all your dates and timestamps are going to come in this way then as the HINT suggests you need to change the DateStyle.

1. Run SHOW DateStyle, I'm guessing it will return something like ISO, MDY.

2. Change it SET DateStyle = 'ISO, DMY', then your example will work.

Example:

show datestyle ;
 DateStyle 
-----------
 ISO, MDY

select date('22-02-2022');
ERROR:  date/time field value out of range: "22-02-2022"
LINE 1: select date('22-02-2022');
                    ^
HINT:  Perhaps you need a different "datestyle" setting.

set datestyle = 'ISO,DMY';

select date '22-02-2022';
    date    
------------
 2022-02-22