Get data for the last month from the first day to the last day in Firebird

Question

I'm trying to request Firebird to get data for the last month, from the first day to the last day (30 or 31 depending on the month). When I use the code below, it gives me shifted dates from the current one, for example, day 11/14/2017 to 12/13/2017.

The code:

WHERE DATE >= DATEADD(MONTH,-1, CURRENT_TIMESTAMP(2)) AND DATE<= 'TODAY'

Desired result - 11/01/2017 - 11/30/2017

What is the right way to do this?

Answer 1

I do not use Firebird, but I used PostgreSQL quite widely, and I think this should work:

WHERE 
    DATE BETWEEN dateadd(month, -1, CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE) + 1)
    AND CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE)

   CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE) + 1 , dateadd -1 . , CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE) 12/13/2017 - 13 , ​​. . .