How can I extract and display the date range based on Quarter in Oracle?

Question

I'm building a summary table as part of a stored procedure and I have two columns. The first column needs to show the start and the second column needs to show the end of a date range that is based from an input parameter that is a number designating the quarter. I was able to extract the following from AskTom but I have some questions.

Open C1 FOR
SELECT  ( SELECT TRUNC (SYSDATE, 'Q')-1+1 AS 'StartOf' FROM DUAL ),
SELECT  ( SELECT TRUNC(ADD_MONTHS (SYSDATE, +3), 'Q')-2 AS 'EndOf' FROM DUAL )
FROM DUAL;

Question 1. Will the Math here account for LeapYears... I don't think it will but I'm not sure how to handle that.

Question 2. How do I add the input parameter, 'inQuarter' as the specific quarter? I've tried putting it in place of sysdate but I need to reformat it into date first I think?

Thanks in advance for any responses.

Why do you have -1+1? - that's just going to cancel out. Where do you need to account for leap years? The quarter dates are 1-JAN, 1-APR, 1-JUL, and 1-OCT - that is what you would use for specific quarters. — David Faber, Mar 13 '12 at 13:57
@dee - What does `inQuarter` represent? You say that it is a "number designating the quarter" which presumably means something very specific to you but it's not clear what that is to me. Do you mean that you are passing a number like 1 that is supposed to be interpreted as the first quarter of the current year? Or that you have a number like 200403 which is supposed to be interpreted as the 3rd quarter of 2004? Or something else? — Justin Cave, Mar 13 '12 at 14:03
Apologies for not clarifying properly. inQuarter is a single digit number 1-4 that signifies the quarter.. that also reminds me that there is also inYear which is YYYY both of those need to give me the first day and the last day of the quarter they specify. Thanks for fast responses. — dee, Mar 13 '12 at 14:30
@Faber. I'm not sure how to explain the math myself but +1-1 gives me 2012-01-01 while just -1 returns 2011-12-31 and +1 returns 2012-01-02. I guess the numbers here work at a formatting level more so than a math level, This is why I'm curious about leap years working or not. — dee, Mar 13 '12 at 14:36

score 3 · Accepted Answer · answered Mar 13 '12 at 14:45

3

Tom Kyte has givven you the answer: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:250288900346075009

Open C1 FOR 
select add_months( dt, (inQuarter-1)*3 ),
       last_day(add_months( dt, (inQuarter-1)*3+2 ) )
from (
  select to_date( inYear || '0101', 'yyyymmdd' ) dt
  from dual)

answered Mar 13 '12 at 14:45

A.B.Cade

16,735
1
37
53

Thanks, A.B. err Tom.... err A.B. ... This worked exactly right. I started at AskTom as well... not sure how I missed this. – dee Mar 13 '12 at 15:14

score 1 · Answer 2 · 2012-03-13T15:05:05.173

1

I suggest:

Open C1 FOR
SELECT TRUNC (d_inQuarter, 'Q') AS "StartOf",
       TRUNC(ADD_MONTHS (d_inQuarter, +3), 'Q') AS "EndOf"
FROM (SELECT add_months(to_date(to_char(i_yr)||'-01-01','YYYY-MM-DD'), (i_q-1)*3)
      AS d_inQuarter FROM DUAL);

- with integer parameters i_yr and i_q representing the year and quarter, respectively.

Note that EndOf will represent midnight on the first day of the next quarter, so any selection should be based on conditions < "EndOf", not <= "EndOf". (This should ensure that all times on the last day of the quarter are included.)

edited Mar 13 '12 at 15:05

answered Mar 13 '12 at 14:28

Thanks Mark, This definitely looks to me to take care of my leap year question. Any idea how I can extract a date from my two given parameters? – dee Mar 13 '12 at 14:48

score 1 · Answer 3 · answered Mar 13 '12 at 14:46

You can convert a numeric year and numeric quarter parameter to a DATE

SELECT add_months( trunc( to_date( to_char( <<numeric year>> ),
                                   'YYYY' ),
                          'YYYY' ),
                   3 * <<numeric quarter>> ) first_of_quarter,
       add_months( trunc( to_date( to_char( <<numeric year>> ),
                                   'YYYY' ),
                          'YYYY' ),
                   4 * <<numeric quarter>> ) - 1 last_of_quarter,
  FROM dual

The time component of both dates will be midnight on the last day of the quarter (i.e. 24 hours before the beginning of the next quarter). You may want the last of the quarter to be 23:59:59 on the last day of the quarter if you want the range to be inclusive of all possible dates in the quarter.

All three answers provided all work. Thanks for everyone's time. — dee, Mar 13 '12 at 15:13

How can I extract and display the date range based on Quarter in Oracle?

3 Answers3