How to find last day of the monthyear in server jobs

[kirankumarreddydesireddy]

Hi

We are working on Datastage 7.5v server jobs on windows environment.

We had a requirement to load the last day of the particular monthyear from the source.

Input :

MONTHYEAR

01/2011
02/2011
03/2011
06/2011
08/2011
01/2012
02/2012


Output :

MONTHYEAR LASTDAYMONTHYEAR

01/2011 31/01/2011
02/2011 28/02/2011
03/2011 31/03/2011
06/2011 30/06/2011
08/2011 30/08/2011
01/2012 31/01/2012
02/2012 29/02/2012

In server jobs,we dont have any function to find the last day of the monthyear.

we were able to write custom code like the MONTHYEAR[1,2] = '01' then '31/':'01/2011' and MONTHYEAR[1,2] = '02' then '28/':'02/2011' and so on....

However,this doesnot take leap year into consideration.Can anyone let me know if there is any way to handle this?




Thanks
Kiran

[kirankumarreddydesireddy]

Hi,

I had got the idea....

I had put one more constraint in February check i.e mod(YEAR,4) = 0 Then '29/':MONTHYEAR Else '28/':MONTHYEAR

Please correct if I was wrong.....



Thanks
Kiran

[TonyInFrance]

Are you extracting your data from an RDBMS or some other source. I have a similar requirement where my data is stored in Oracle. in my case I am simply using the Oracle function LAST_DAY.

I feel its much more convenient to transform as much of my data as possible outside my ETL.

[ray.wurlod]

Why not use the MONTH.LAST transform?

[kirankumarreddydesireddy]

Thanks Ray.

MONTH.LAST transform is useful in this case.



Thanks
Kiran