How do I find data from this day exactly one year ago in oracle?-CodePudding

Merry Christmas,

I am using the below query to get the prices, but my requirement is to get the data for last one year from trunc(sysdate). i have tried using DATEADD function but its gives me an error

cast(p.asof as DATE) = cast(DATEADD(Year, -1, GETDATE()) as DATE)

select idvalue, p.asof as DATE_, p.instrument, p.price
from instruments i
inner join prices_equity_closing p on p.instrument = i.pkey
inner join instruments_ids id on i.ids = id.idset
where
id.idvalue in ('MRVE3.SA')
and id.idtype in ('RIC')
and p.asof = trunc(sysdate)-1
order by i.exchange, i.type, p.asof desc;

Can anyone please help, what all changes i require to get the desired result ?

CodePudding user response：

use add_months and sysdate:

select add_months(sysdate,-12) x from dual

CodePudding user response：

You can subtract an INTERVAL of 12 months from the current date.

... 
where
id.idvalue in ('MRVE3.SA')
and id.idtype in ('RIC')
and p.asof >= trunc(sysdate) - interval '12' month
order by i.exchange, i.type, p.asof desc;

CodePudding user response：

or we can simply use and p.asof > trunc(sysdate)-365