FiscalYearDateQuerrySQL

I have a stock item table like this


stock_item
-----------
stock_code  varchar2(10)
discontinued varchar2(1)
date_discontined  date

I want to report on items that are not disconitnued or that have been discontinued this fiscal year only. I want to exclude any items discontinued previous fiscal year or before.

Disconitnued = 'Y'
Not discontinued = NULL

What is the best sql for that. I was thinking of somethig like this

select stock_code from stock_item where discontinued IS NULL OR (discontinued='Y' and date_discontinued >= '01-OCT-'||to_char(to_char(trunc(sysdate('YYYY'))-1)

since there is no function that subtracts 3 months from 1st day of year.

ADD_MONTHS only adds months.
sam15Asked:
Who is Participating?
 
sdstuberCommented:
how about this?

add_months will work just fine


select stock_code from stock_item where discontinued IS NULL OR (discontinued='Y' and date_discontinued >=add_months(trunc(sysdate,'yyyy'),-3)
0
 
sam15Author Commented:
Yes, you are correct. I think i had wrong place of parenthesis for TO_CHAR.

you are a genius!
0
 
sdstuberCommented:
don't need to_char at all,  but if you were going to do that (and I don't recommend it)


it would look something like


 to_date( '01-OCT-'||(to_char(sysdate,'yyyy')-1),'dd-MON-yyyy')


or more formally

to_date( '01-OCT-'||to_char((to_number(to_char(sysdate,'yyyy'))-1)),'dd-MON-yyyy')
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.