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 SawyerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PHIL SawyerAuthor Commented:
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
0
sdstuberCommented:
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
0
PHIL SawyerAuthor Commented:
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
0
sdstuberCommented:
simply add 1 day before truncating or other date math

SELECT ADD_MONTHS(TRUNC(your_date + 1, 'q'), -3) start_of_prev_quarter,
       TRUNC(your_date + 1, 'q') - 1 / 86400 end_of_prev_quarter
  FROM DUAL;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PHIL SawyerAuthor Commented:
Great - many thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.