PHIL Sawyer
asked on
Last Clear Calendar Quarter
Hi
Want to be able to find "last calendar quarter" data from a given date parameter.
eg
Select t1.* from mytable t1 where t1.my_date between ???? :pi_date ???????
So, if I use a date paremeter and select 07/10/2014 then I want data returned between 1st April to end of June 2014 as that would be the last clear calendar quarter from my date parameter and if I selected date 01/02/2014 then I want data returned between 1st October to end December 2013 etc etc
Regards
Want to be able to find "last calendar quarter" data from a given date parameter.
eg
Select t1.* from mytable t1 where t1.my_date between ???? :pi_date ???????
So, if I use a date paremeter and select 07/10/2014 then I want data returned between 1st April to end of June 2014 as that would be the last clear calendar quarter from my date parameter and if I selected date 01/02/2014 then I want data returned between 1st October to end December 2013 etc etc
Regards
SELECT ADD_MONTHS(TRUNC(SYSDATE, 'q'), -3) start_of_prev_quarter,
TRUNC(SYSDATE, 'q') - 1 / 86400 end_of_prev_quarter
FROM DUAL;
substitute your date variable for SYSDATE
TRUNC(SYSDATE, 'q') - 1 / 86400 end_of_prev_quarter
FROM DUAL;
substitute your date variable for SYSDATE
ASKER
Thats great - one final comment . If I choose 30/09/2014 then your solution will select dates from
01/04/2014 to 30/06/2014 23:59:59 - which is correct. However, is it possible that if I happen to select a day on my date parameter such as the last day of any full calendar quarter eg (30/09/2014) it would be great if the data returned would be between 01/07/2014 to 30/09/2014. Is this possible?
Regards
01/04/2014 to 30/06/2014 23:59:59 - which is correct. However, is it possible that if I happen to select a day on my date parameter such as the last day of any full calendar quarter eg (30/09/2014) it would be great if the data returned would be between 01/07/2014 to 30/09/2014. Is this possible?
Regards
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Great - many thanks
ASKER
Want to be able to find "last calendar quarter" data from a given date parameter.
eg
Select t1.* from mytable t1 where t1.my_date between ???? :pi_date ???????
So, if I use a date paremeter and select 07/09/2014 then I want data returned between 1st April to end of June 2014 as that would be the last FULL clear calendar quarter from my date parameter and if I selected date 01/02/2014 then I want data returned between 1st October to end December 2013 as that would be the last FULL clear calendar quarter from my date parameter etc etc