0

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

Happy Coder
  • 4,255
  • 13
  • 75
  • 152
  • Does this answer your question? [Is there a function that takes a year, month and day to create a date in PostgreSQL?](https://stackoverflow.com/questions/15317807/is-there-a-function-that-takes-a-year-month-and-day-to-create-a-date-in-postgre) – Álvaro González May 06 '22 at 07:14

2 Answers2

0

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.

Jonas Metzler
  • 4,517
  • 1
  • 5
  • 17
0

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

make_date(2021,9,1)