Getting the last date of month using the first date of month

[sga_venkatesan]

Hi,
I have to get the last date of month using the first date of month.

eg:

If the date is 20050401(YYYYMMDD), I need the value 20050430.

Is there any function in datastage to do this or is there any other way?

Kindly help me.

[jhmckeever]

You could use a Field lookup technique as detailed by Ray in this post:

viewtopic.php?t=102543

Ray's technique looks up Month names, but you could just as easily look up month end-dates.

J.

[kcbland]

Welcome aboard! The easiest method is to take a date, say 2006-07-14, add one to the month and set the day to -01, thus 2006-08-01. Then, subtract one from the date. To do this, write a small DS Function. Here's a good start for you, it assumes that Arg1 contains a YYYY-MM-DD date:

Code: Select all

CurrentDate = Arg1
YYYY=FIELD(CurrentDate,"-",1)
MM=FIELD(CurrentDate,"-",2)
DD = "1"
MM += 1
If MM = 13 Then
   YYYY += 1
   MM = "1"
End
NewDate = YYYY:"-":FMT(MM,'R%2'):"-":FMT(DD,'R%2')
LastDayOfMonth=OCONV(ICONV(NewDate,'D-YMD[4,2,2]') - 1, 'D-YMD[4,2,2]')

[sga_venkatesan]

Thank U Kenneth Bland...

[ray.wurlod]

MONTH.LAST Transform (supplied with the product)?