Thursday, July 14, 2011

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;

14 comments:

Suman. said...

Thank you for the Posts..
Could you plz help me in writing the Date in the following format
Start Date of previous Year = '01/01/2010 12:00 AM'
End Day of Previous Year = '01/31/2010 11:59 PM'

I have written the code to round of the First day of current yr "TO_CHAR(TRUNC(SYSDATE),'DD/MM/YYYY HH12:MI:SS AM')", but could not able to find the last day of the year along with the time.
TO_CHAR(LAST_DAY(ADD_MONTHS(TRUNC(TRUNC(SYSDATE , 'Year')-1 , 'Year'),11)), 'DD/MM/YYYY HH12:MI:SS AM')

Vishal said...

The below will bring the last date with time. It will always be 11:59 PM so i have given like this

select TO_CHAR(LAST_DAY(ADD_MONTHS(TRUNC(TRUNC(SYSDATE, 'Year') - 1, 'Year'),
11)),
'DD/MM/YYYY') || ' 11:59 PM'
from dual

abbas said...

very helpful post.

abbas said...

nice & very helpful post.

Anonymous said...

thank you, your post is very helpful

IKSA said...

Thank you so much, very kind of you. I've bookmarked this post for later use. Thanks!!!

Anonymous said...

Its very helpful

Anonymous said...

Excellent Work Dude!

Anonymous said...

Great work and will be very useful. Thank you

akshat said...

Great work Vishal, excellent compilation!

One comment, You can also acheive the 11:59:59 timestamp as follows...
If the seconds are not needed then modify the date format.

select TO_CHAR(LAST_DAY(ADD_MONTHS(TRUNC(TRUNC(SYSDATE, 'Year') - 1, 'Year'),
11))-0.00001,
'DD/MM/YYYY HH:MI:SS')
from dual

Thanks,
AJ

Vipin Kumar said...

Thanks for the useful Post.
Could you plz help me to find the last and first date if i have financial year and Quarter.

Vipin Kumar said...

Thanks for useful Post.
Could you plz help me to find the last date or first date of any quarter if i have financial year and quarter.

carliecode said...

Thank you for this post!!!

Abi said...

Thanks u so much!!