How to get first day and last date of week, month, quarter, year in Oracle

–First day of current week(sunday)
select TRUNC(SYSDATE, ‘Day’) from dual;
–First day of next week(sunday)
select TRUNC(SYSDATE+7 , ‘Day’) from dual;
–First day of previous week(sunday)
select TRUNC(SYSDATE-7 , ‘Day’) from dual;
–First day of current month
select TRUNC(SYSDATE , ‘Month’) from dual;
–First day of previous month
select TRUNC(TRUNC(SYSDATE , ‘Month’)-1 , ‘Month’) from dual;
–First day of next month
select TRUNC(LAST_DAY(SYSDATE)+1 , ‘Month’) from dual;
–First day of current year
select TRUNC(SYSDATE , ‘Year’) from dual;
–First day of previous year
select TRUNC(TRUNC(SYSDATE , ‘Year’)-1 , ‘Year’) from dual;
–First day of next year
select ADD_MONTHS(TRUNC(SYSDATE , ‘Year’),12) from dual;
— First Day of Current quater
select TRUNC(SYSDATE , ‘Q’) from dual;
—  First Day of Previous Quarter
select ADD_MONTHS(TRUNC(SYSDATE , ‘Q’),-3) from dual;
—  First Day of Next Quarter
select ADD_MONTHS(TRUNC(SYSDATE , ‘Q’),3) from dual;

–Last day of current week(sunday)
select TRUNC(SYSDATE, ‘Day’)+6 from dual;
–Last day of next week(sunday)
select TRUNC(SYSDATE+7 , ‘Day’)+6 from dual;
–Last day of previous week(sunday)
select TRUNC(SYSDATE-7 , ‘Day’)+6 from dual;
–Last day of current month
select LAST_DAY(TRUNC(SYSDATE , ‘Month’)) from dual;
–Last day of previous month
select LAST_DAY(TRUNC(TRUNC(SYSDATE , ‘Month’)-1 , ‘Month’)) from dual;
–Last day of next month
select LAST_DAY(TRUNC(LAST_DAY(SYSDATE)+1 , ‘Month’)) from dual;
–Last day of current year
select LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE , ‘Year’),11)) from dual;
–Last day of previous year
select LAST_DAY(ADD_MONTHS(TRUNC(TRUNC(SYSDATE , ‘Year’)-1 , ‘Year’),11)) from dual;
–Last day of next year
select LAST_DAY(ADD_MONTHS(TRUNC(TRUNC(SYSDATE , ‘Year’)-1 , ‘Year’),-13)) from dual;
— Last Day of Current quater
select LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE , ‘Q’),2)) from dual;
—  Last Day of Previous Quarter
select TRUNC(SYSDATE , ‘Q’)-1 from dual;
—  Last Day of Next Quarter
select LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE , ‘Q’),5)) from dual;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: