CODEWITHSUNDEEP
sqloracle-databasedateto-date
Soji
Soji

Reputation: 269

Date and quarter SQL ORACLE

I am currently working on oracle sql and have some issues managing dates and quarters.

The first thing I try to do is given a quarter and a year I would like to know the number of days of the quarter. For example, quarter 1 in 2013 had 90 days but in 2012 had 91 days.

The second thing I would like to do is convert a date dd/qq/yyyy to a date dd/mm/yyyy.

For instance, 60/2/2013 gives 30/5/2013. I am a beginner in Oracle so any help or function names will be highly appreciated.

Thanks

Upvotes: 4

Views: 13723

Answers (2)

Boneist
Boneist

Reputation: 23588

For your first question, hopefully this will give you some idea of what to do:

with sample_data as (select 2012 yr, 1 quarter from dual union all
                     select 2012 yr, 2 quarter from dual union all
                     select 2012 yr, 3 quarter from dual union all
                     select 2012 yr, 4 quarter from dual union all
                     select 2013 yr, 1 quarter from dual union all
                     select 2013 yr, 2 quarter from dual)
---- end of mimicking a table called "sample_data"; see query below:
select yr,
       quarter,
       add_months(to_date('01/01/'||yr, 'dd/mm/yyyy'), (quarter - 1)*3) qtr_st,
       add_months(to_date('01/01/'||yr, 'dd/mm/yyyy'), quarter * 3) - 1 qtr_end,
       add_months(to_date('01/01/'||yr, 'dd/mm/yyyy'), quarter * 3) - add_months(to_date('01/01/'||yr, 'dd/mm/yyyy'), (quarter - 1)*3) diff
from   sample_data;

        YR    QUARTER QTR_ST     QTR_END          DIFF
---------- ---------- ---------- ---------- ----------
      2012          1 01/01/2012 31/03/2012         91
      2012          2 01/04/2012 30/06/2012         91
      2012          3 01/07/2012 30/09/2012         92
      2012          4 01/10/2012 31/12/2012         92
      2013          1 01/01/2013 31/03/2013         90
      2013          2 01/04/2013 30/06/2013         91

N.B. Because you're including the day of the start_date in the count, the difference is effectively how many days between the 1st of the quarter and the 1st of the next quarter, or qtr_end - qtr_st + 1 from my query above.

For your second question, here's one way:

with sample_data as (select '60/2/2013' dy_qtr_fmt from dual union all
                     select '60/02/2013' dy_qtr_fmt from dual union all
                     select '01/1/2013' dy_qtr_fmt from dual union all
                     select '1/1/2013' dy_qtr_fmt from dual)
---- end of mimicking a table called "sample_data"; see query below:
select dy_qtr_fmt,
       add_months(year_st, (qtr-1)*3) + num_days_in_qtr - 1 dt
from   (select dy_qtr_fmt,
               to_date('01/01/'||substr(dy_qtr_fmt, -4), 'dd/mm/yyyy') year_st,
               to_number(substr(dy_qtr_fmt, instr(dy_qtr_fmt, '/', 1, 1) + 1, instr(dy_qtr_fmt, '/', 1, 2) - instr(dy_qtr_fmt, '/', 1, 1) -1)) qtr,
               to_number(substr(dy_qtr_fmt, 1, instr(dy_qtr_fmt, '/', 1, 1) - 1)) num_days_in_qtr
        from   sample_data);

DY_QTR_FMT DT        
---------- ----------
60/2/2013  30/05/2013
60/02/2013 30/05/2013
01/1/2013  01/01/2013
1/1/2013   01/01/2013

Upvotes: 1

davegreen100
davegreen100

Reputation: 2115

this gives the number of days in the current quarter, playing around with the add_months should allow you to find the length of other quarters

select (add_months(trunc(sysdate,'q'),3) - 1) - trunc(sysdate,'q') 
from dual;

Upvotes: 2

Related Questions