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
Solved

Last Clear Calendar Quarter

Posted on 2014-10-30
5
201 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 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 Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that useā€¦
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, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

840 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