Giorgos Giorgos - 3 months ago 15
SQL Question

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;

Answer

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