Hi ,
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
Toad for oracle :12:8:0:49.
I am trying to filter data for fiscal year july 01 through june 30 , I am using bind variable to choose input value (please check sql query posted below)
if input bind value current month(9/10/2017) is greater or equal to 7 then fiscal start date will be 7/01/2017 and end date will be 8/30/2018 ,
if input bind value current month(6/20/2017) is less than or equal to 7 then fiscal start date will be 7/01/2016 and end date will be 8/30/2017 ,
I can able to achieve this using below sql script , but problem is with bind variable and if i choose input bind value as varchar2 then data will generated , if choose date data type as input value then throwing error ,
I want to pass input bind value as date data type ,
Any suggestion please?
SELECT case when extract(month from to_date(:dt,'mm/dd/yyyy'))>=7 then to_date('7/01'||(extract(year from to_date(:dt,'mm/dd/yyyy'))),'mm/dd/yyyy')
when extract(month from to_date(:dt,'mm/dd/yyyy'))<7 then to_date('7/01'||(extract(year from to_date(:dt,'mm/dd/yyyy'))-1),'mm/dd/yyyy') end fis_start,
case when extract(month from to_date(:dt,'mm/dd/yyyy'))>=7 then to_date('6/30'||(extract(year from to_date(:dt,'mm/dd/yyyy'))+1),'mm/dd/yyyy')
when extract(month from to_date(:dt,'mm/dd/yyyy'))<7 then to_date('6/30'||(extract(year from to_date(:dt,'mm/dd/yyyy'))),'mm/dd/yyyy') end fis_end,
to_date(:dt,'mm/dd/yyyy') mnthly
FROM fac
Thanks