Hi,

Hope all have started to enjoy the summer (also may be struggling with too much heat waves).

I have a question on how to construct a query where I use a date like 'End of last year' (EOLY)

I run this query once a month for the previous month data

The End of last year works fine when I run this query from Feb to Dec ( so for example, now if I run the query the EOLY will be '31/12/2018'

But when I run this in January, EOLY needs to be '31/12/2017' because I am pulling the data of last month i.e. 2018

how do I adjust the query with something like by using the sysdate?

thanks in advance
Jain

alter system set fixed_date = '2019-01-01';

select trunc ( add_months ( sysdate, -1 ), 'y' ) - 1
from   dual;

TRUNC(ADD_MONTHS(SYSDATE,-1),'Y')-1   
31-DEC-2017 00:00:00

alter system set fixed_date = '2019-02-02';

select trunc ( add_months ( sysdate, -1 ), 'y' ) - 1 
from   dual;

TRUNC(ADD_MONTHS(SYSDATE,-1),'Y')-1   
31-DEC-2018 00:00:00 

alter system set fixed_date = none;