All Forums Database
rlaskar 18 posts Joined 03/06
28 Mar 2006
Current Month End Date

Hi All,I was trying to identify current month end date. Let me know any sql syntax toidentify the current month end date.Thanks,rlaskar

28 Mar 2006

Try thisselect cast(current_date as date) - (extract (day from cast(current_date as date)) - 1) + interval '1' month - 1I am sure there are many more methods. (some of our guru's will provide)The logic i am using is:get the date, find the first day of the month, add one month to get the next months first day and then substract one day from the next months start date.select cast('2006-03-28' as date) - (extract (day from cast('2006-03-28' as date)) - 1) + interval '1' month - 12006-03-31

vikas sharma 2 posts Joined 03/06
29 Mar 2006

HiTry this .select add_months((current_date - extract(day from current_date)+1),1)-103/31/2006Vikas

23 Aug 2012

SEL ADD_MONTHS(CURRENT_DATE-EXTRACT(DAY FROM CURRENT_DATE),1);

bertin88 4 posts Joined 05/11
04 Sep 2012

SEL ADD_MONTHS(CURRENT_DATE-EXTRACT(DAY FROMCURRENT_DATE),1);

will have problem for date '2012-03-01'(March). Solution is  Vikas's query.

Adeel Chaudhry 773 posts Joined 04/08
16 Apr 2014

True, the correct query is:
 
SELECT ADD_MONTHS(cast('2014-03-24' as date)-EXTRACT(DAY FROM cast('2014-03-24' as date))+1,1)-1
 
Other one also messes up the leap year Feb-Mar.

-- If you are stuck at something .... consider it an opportunity to think anew.

You must sign in to leave a comment.