Postgres: How to automatically fix date/time field value out of range inside the query

Question

How one can make Postgres treat a little stupid about the date which actually does not occur on the Gregorian calendar?

SELCET date, value 
FROM table 
WHERE date BETWEEN 2022-01-01 AND 2022-02-31

You know that the query works fine when use the first day of the next month:

SELCET date, value FROM table WHERE date BETWEEN 2022-01-01 AND 2022-03-01

My question is how we can handle such a situation on the database side instead of the App side. I mean we can prevent such values from getting into the database inside our App. However, it will be handier if we can fix them inside queries. For example, I seek a procedure that converts invalid dates to the maximum acceptable date: 2022-02-31 --> 2022-02-28

Answer 1

Use date arithmetic to calculate the last day of a month. For the current month, that would be:

SELECT CAST(date_trunc('month', current_timestamp) + INTERVAL '1 month' AS date) - 1;

Answer 2

select c.bdate,
    case
        when length(c.dot_three)=4 and (c.dot_one::int between 13 and 31) and c.dot_two::int<=12 then concat(c.dot_three,'-',c.dot_two,'-',c.dot_one)
        when length(c.dot_three)=4 and c.dot_two::int<=12 and c.dot_one::int<=12 then concat(c.dot_three,'-',c.dot_two,'-',c.dot_one)
        when length(c.dot_three)=4 and (c.dot_two::int between 13 and 31) and c.dot_one::int<=12 then concat(c.dot_three,'-',c.dot_one,'-',c.dot_two)
        when length(c.dot_one)=4 and (c.dot_three::int between 13 and 31) and c.dot_two::int<=12 then concat(c.dot_one,'-',c.dot_two,'-',c.dot_three)
        when length(c.dot_one)=4 and c.dot_two::int<=12 and c.dot_one::int<=12 then concat(c.dot_one,'-',c.dot_two,'-',c.dot_three)
        when length(c.dot_one)=4 and (c.dot_two::int between 13 and 31) and c.dot_three::int<=12 then concat(c.dot_one,'-',c.dot_three,'-',c.dot_two)
        when length(c.slash_three)=4 and (c.slash_one::int between 13 and 31) and c.slash_two::int<=12 then concat(c.slash_three,'-',c.slash_two,'-',c.slash_one)
        when length(c.slash_three)=4 and c.slash_two::int<=12 and c.slash_one::int<=12 then concat(c.slash_three,'-',c.slash_two,'-',c.slash_one)
        when length(c.slash_three)=4 and (c.slash_two::int between 13 and 31) and c.slash_one::int<=12 then concat(c.slash_three,'-',c.slash_one,'-',c.slash_two)
        when length(c.slash_one)=4 and (c.slash_three::int between 13 and 31) and c.slash_two::int<=12 then concat(c.slash_one,'-',c.slash_two,'-',c.slash_three)
        when length(c.slash_one)=4 and c.slash_two::int<=12 and c.slash_three::int<=12 then concat(c.slash_one,'-',c.slash_two,'-',c.slash_three)
        when length(c.slash_one)=4 and (c.slash_two::int between 13 and 31) and c.slash_three::int<=12 then concat(c.slash_one,'-',c.slash_three,'-',c.slash_two)
        else ''
    end as gdate
from (
    select t.bdate,split_part(bdate,'.',1) as dot_one, split_part(bdate::text, '.',2) as dot_two,
        split_part(bdate::text, '.',3) as dot_three,
        split_part(bdate,'/',1) as slash_one, split_part(bdate::text, '/',2) as slash_two,
        split_part(bdate::text, '/',3) as slash_three
    from
    (SELECT unnest(array['31.08.2014','16.09.2014','13.12.2015','10/9/2018','4/4/2019','2022/4/4','3/2/2018','27/8/2019','15/2/2019','26/11/2019']) as bdate)t
)c