DSXchange

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

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

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.

Why not use the MONTH.LAST transform?

Thanks Ray.

MONTH.LAST transform is useful in this case.



Thanks
Kiran