Page 1 of 1
Getting the last date of month using the first date of month
Posted: Mon Nov 20, 2006 9:51 am
by 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.
Posted: Mon Nov 20, 2006 9:59 am
by 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.
Posted: Mon Nov 20, 2006 10:06 am
by 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]')
Posted: Mon Nov 20, 2006 10:14 am
by sga_venkatesan
Thank U Kenneth Bland...
Posted: Mon Nov 20, 2006 2:08 pm
by ray.wurlod
MONTH.LAST Transform (supplied with the product)?