I need to write this kind of code
BETWEEN '2022-02-01' AND '2022-02-28'
but to be relative and selecting last month
I tried it this way
BETWEEN TO_CHAR(current_date - interval '1' month, 'yyyy-mm-01')::date
AND TO_CHAR(TO_CHAR(current_date, 'yyyy-mm-01')::date - interval '1' day, 'yyyy-mm-dd')::date
but I think there might be better solution to this.
>Solution :
Don’t convert date values to strings for comparisons. Always use "real" date values.
To get the start of the last month, you can use a combination of date_trunc() and subtracting an interval
The start of the last month is the start of "this month" minus one month:
date_trunc('month', current_date) - interval '1 month'
And the end of the last month is the start of this month minus one day:
date_trunc('month', current_date) - interval '1 day'
You didn’t tell us which data type your column has that you want to compare, but typically it’s better to use >= and < rather than between – especially if that column is a timestamp.
where the_column >= date_trunc('month', current_date) - interval '1 month'
and the_column < date_trunc('month', current_date)