Get last month data from first day until last day in Firebird

Question

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

The code:

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

The desired output is 11/01/2017 - 11/30/2017

What is the correct way to do it?

Answer 1

I don't use Firebird but I've used PostgreSQL fairly extensively 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)

Explanation CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE) + 1 should go back to the first of this month and dateadd with -1 month should take it to the previous month. Then if you're between CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE) or in other words 12/13/2017 - 13 days that should be the last day of November. Crossing my fingers. Good luck.