Getting YTD from sysdate in Oracle DB

[vaibhav_rathore]

Hi Experts,

I have a requirement where i want to display YTD in webi where the DB is Oracle and i want this in sql query of the report.

Currently its using syntax:

For YTD:- select TRUNC (sysdate, 'year')-1 /* its giving date as 31 Dec 2021 in DB when ran on 13th Jan 2022 , while it must return 1/1/2021 as per requirement */

whereas

For MTD:- select TRUNC (sysdate, 'mon')-1 /* is giving 31 Dec 2021 in DB which is correct for MTD */

Please help me in getting this YTD issue solved.

Database is Oracle.

Thanks

Vaibhav

[JamesZ · Accepted solution]

Please test add_months(sysdate, -1), which should help.

fyi,
https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions004.htm

[vaibhav_rathore · Accepted solution]

Hi James,

If i run the report then date returned must be 12/31/2020 (current update from user) as per todays sysdate.

and if i am using select trunc(add_months(sysdate,-1),'y') from dual; its giving me 1/1/21

Please advise.

Thanks

Vaibhav

[JamesZ · Accepted solution]

sorry not very clear, how about below query?

select sysdate from dual;
select add_months(sysdate,-1) from dual?

not correct?