Thursday, August 9, 2012

add_months Function Quarter start date and end date


how can i get the start date n end date of quarter?eg. if i pass date as 30/07/2012 then start date should be 01/07/2012 to 30/09/2012,If i pass 21/12/2012 then it should be 01/10/2012 to
31/12/2012


SQL> select TRUNC(sysdate+1, 'Q'),TRUNC(ADD_MONTHS(sysdate, +3), 'Q')-1 from dual;

TRUNC(SYS TRUNC(ADD
--------- ---------
01-JUL-12 30-SEP-12



SQL> select TRUNC(to_date('30-jul-12'), 'Q'),TRUNC(ADD_MONTHS('30-jul-12',+3), 'Q')-1 from dual;

TRUNC(TO_ TRUNC(ADD
--------- ---------
01-JUL-12 30-SEP-12


SQL> select TRUNC(to_date('21-dec-12'),'Q'),TRUNC(ADD_MONTHS('21-dec-12',+3), 'Q')-1 from dual;

TRUNC(TO_ TRUNC(ADD
--------- ---------
01-OCT-12 31-DEC-12



references

http://www.dba-oracle.com/job_scheduling/dates_times.htm

http://www.oracle.com/technetwork/issue-archive/2012/12-jan/o12plsql-1408561.html
http://www.techonthenet.com/oracle/functions/add_months.php

http://www.sql-server-helper.com/functions/get-first-day-of-quarter.aspx

http://www.tek-tips.com/viewthread.cfm?qid=1635253
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3868465700346538981



No comments:

Post a Comment

Followers