Solved

Last Clear Calendar Quarter

Posted on 2014-10-30
5
198 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

Suggested Solutions

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…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

803 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