CODEWITHSUNDEEP
sqloracle-database
Giorgos
Giorgos

Reputation: 677

ORACLE find the first day and last day of a given quarter

Is there a way I can find the first day and last day of a given quarter?

If I select first quarter then I should be able to get the first day and last day of the first quarter

example:

quarter = 1 and year is 2016. expected result is 01/01/2016 - 31/03/2007 (DD/MM/YYYY format).

for example: 

select(quarterdatestart, quarterdatedate) where year = 2016 and quarter = 1;

Upvotes: 1

Views: 10558

Answers (3)

Justin Cave
Justin Cave

Reputation: 231861

trunc( sysdate, 'Q' )

will give you the first day of the current quarter.

trunc( add_months(date '0001-01-01', (y-1)*12 + (q-1)*3), 'Q' )

will give you the first day of quarter q in year y. An example of calling this with different year and quarter values

with x as (
  select 2016 y, 1 q from dual union all
  select 2016, 2 from dual union all
  select 2015, 4 from dual
)
select trunc( add_months(date '0001-01-01', (y-1)*12 + (q-1)*3), 'Q' ) 
  from x

To get midnight on the last day of the quarter, simply add three months and subtract a day

<<first day of quarter>> + interval '3' month - interval '1' day

or

add_months(<<first day of quarter>>, 3) - 1

I generally find the interval syntax a bit more expressive but the older syntax is more compact.

If you want the last date in the quarter (23:59:59 on the last day of the quarter), simply subtract 1 second rather than 1 day.

Upvotes: 6

AT-2017
AT-2017

Reputation: 3149

Yes, you can do with following:

First day of the current year:

SELECT TRUNC (SYSDATE , ‘YEAR’) FROM DUAL;

Last day of the current year:

SELECT ADD_MONTHS(TRUNC (SYSDATE, ’YEAR’), 12) - 1 FROM DUAL;

By the way, you can use the following to find the desired output:

with q(qtr) as( 
      select add_months( DATE '2016-01-01',
      (level - 1) * 3 ) from dual connect by level <= 4 )
       select qtr as first_day,
      last_day(add_months(qtr, 2)) as last_day 
from q

Upvotes: 2

PKey
PKey

Reputation: 3841

Try this 

SELECT  ADD_MONTHS( to_date('01/01/2017','dd/mm/rrrr') ,3*(:quarter-1)) first, ADD_MONTHS( to_date('01/01/2017','dd/mm/rrrr') ,3*:quarter)-1 last   FROM dual

Upvotes: 0

Related Questions