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

PHIL Sawyer

ASKER

Error on my part - see below for revised logic.

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

Sean Stuber

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

PHIL Sawyer

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

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PHIL Sawyer

ASKER

Great - many thanks