Current Month End Date

Database

Current Month End Date

Hi All,

I was trying to identify current month end date. Let me know any sql syntax to
identify the current month end date.

Thanks,
rlaskar
5 REPLIES

Re: Current Month End Date

Try this
select cast(current_date as date) - (extract (day from cast(current_date as date)) - 1) + interval '1' month - 1

I 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 - 1

2006-03-31

Re: Current Month End Date

Hi

Try this .

select add_months((current_date - extract(day from current_date)+1),1)-1

03/31/2006

Vikas

Re: Current Month End Date

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

Re: Current Month End Date

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

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

Teradata Employee

Re: Current Month End Date

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.