PostgreSQL TO_DATE date/time field value out of range-CodePudding

I am using TO_DATE in one of my PostgreSQL functions and it is throwing errors like date/time field value out of range: "2021901". This is happening for the months of January to September as I need to add zeros in front of them. So I tried to execute a simple select query there as follows as I am using the same syntax in function.

SELECT TO_DATE(2021::varchar||09::varchar||'01','YYYYMMDD')

This is also giving me the error

ERROR:  date/time field value out of range: "2021901"
SQL state: 22008

Now if I change the month to October, November, or December it works fine, but for all the other months, it is showing this error. I am actually new to Postgres and not sure how to fix this. It would be very much helpful if someone can point me in the right direction. Thanks

CodePudding user response：

If your input values are numbers (integer), another alternative is to use make_date()

make_date(2021,9,1)

CodePudding user response：

A better and easier way would be to just provide the date correctly and use TO_DATE, so as example do this:

SELECT TO_DATE('2021-09-01', 'YYYY-MM-DD')

If you really want to go your way, you can force a leading zero by using TO_CHAR, like this:

SELECT TO_DATE(2021::varchar||TO_CHAR(09, 'fm00')||'01','YYYYMMDD')

But I recommend to take the first propose.