end of month dates

Database
Enthusiast

end of month dates

Hi all,

I have a column(EFFECTIVE_START_DATE) which has to be transformed to INFOMATION_DATE as the last date of the month.

ex : if my source dates are 26th jan 2014, 26th feb 2014 and 26 march 2014, I have to create a view which will contain data in the following way :

for jan-> 1 record

for feb-> 2 records(jan and feb)

for march-> 3 records(jan feb and march)

expected result set:

MONTH_END_DATE          EFFECTIVE_START_DATE

2014-31-01                     2014-26-01

2014-28-02                     2014-26-01

2014-28-02                     2014-26-02

2014-31-03                     2014-26-01

2014-31-03                     2014-26-02

2014-31-03                     2014-26-03

Can some one help me acheive the above result set.

Thanks in advance

Cheers,

Subbu

3 REPLIES
Enthusiast

Re: end of month dates

Subbu,

 

You can achieve the functionalty by finding last day of the previous month and then add 1 month to it

SELECT add_months((current_Date - EXTRACT(day from current_Date)),1);

SELECT add_months((cast('2014-02-26' AS DATE)- EXTRACT(day from cast('2014-02-26' AS DATE))),1);

Enthusiast

Re: end of month dates

select t1.col1 as effective_dt,last_day(t2.col1) as information_dt from test t1,test t2

where t2.col1>=t1.col1

order by t2.col1,t1.col1

Table test contains following data

26-01-2014

26-02-2014

26-03-2014

Result of the query is as follows,

effective_dt    information_dt

2014-01-26     2014-01-31

2014-01-26     2014-02-28

2014-02-26     2014-02-28

2014-01-26     2014-03-31

2014-02-26     2014-03-31

2014-03-26     2014-03-31

Kindly let me know if this does not meet your requirement.

Vinay

Enthusiast

Re: end of month dates

thanks for your replies. It worked with a little tweaks according to my requirement.