--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:
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')
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
very helpful post.
nice & very helpful post.
thank you, your post is very helpful
Thank you so much, very kind of you. I've bookmarked this post for later use. Thanks!!!
Its very helpful
Excellent Work Dude!
Great work and will be very useful. Thank you
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
Thanks for the useful Post.
Could you plz help me to find the last and first date if i have financial year and Quarter.
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.
Thank you for this post!!!
Thanks u so much!!
Post a Comment