Oracle APEX Pie Chart displaying current month data

Question

I have successfully created an APEX 'Flash' Pie chart which displays the projects that the logged in user has been working on and the relevant hours they have worked on each project. However the Pie Chart acts as a monthly breakdown and I am currently having problems getting code to work in order for the pie chart to change on a monthly basis. The code I have so far is:

select null link, PROJECT_ADX label, SUM(TS_HOURS) value
from  "TRAK"."CHART"
WHERE USER_ID = :P1_USER_ID
and creation_date between add_months(trunc(sysdate,'mm'),-1) and    last_day(add_months(trunc(sysdate,'mm'),-1))
group by PROJECT_ADX

I have tried different variants of the code below but still can't get it to work.

and creation_date between add_months(trunc(sysdate,'mm'),-1) and last_day(add_months(trunc(sysdate,'mm'),-1))

I would be grateful for any help or tips to put me in the right direction!

Answer 1

Ponder almost had it - depends on whether creation_date includes time component, then you need to go to 11:59:59 on the last_day of the month. There are a dozen ways, but here is one

creation_date between trunc(sysdate, 'mm') and last_day(trunc(sysdate))+1-1/86400