Solved

Last Clear Calendar Quarter

Posted on 2014-10-30
5
195 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
  • 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 73

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 73

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

895 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now