Solved

Last Clear Calendar Quarter

Posted on 2014-10-30
5
209 Views
Last Modified: 2014-10-30
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
0
Comment
Question by:PHIL Sawyer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 

Author Comment

by:PHIL Sawyer
ID: 40413432
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 40413439
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
 

Author Comment

by:PHIL Sawyer
ID: 40413477
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
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 40413538
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
 

Author Comment

by:PHIL Sawyer
ID: 40413557
Great - many thanks
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  â€¦
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question