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.
Getting the last date of month using the first date of month
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 2
- Joined: Mon Nov 20, 2006 8:03 am
-
- Premium Member
- Posts: 301
- Joined: Thu Jul 14, 2005 10:27 am
- Location: Melbourne, Australia
- Contact:
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.
viewtopic.php?t=102543
Ray's technique looks up Month names, but you could just as easily look up month end-dates.
J.
<b>John McKeever</b>
Data Migrators
<b><a href="https://www.mettleci.com">MettleCI</a> - DevOps for DataStage</b>
<a href="http://www.datamigrators.com/"><img src="https://www.datamigrators.com/assets/im ... l.png"></a>
Data Migrators
<b><a href="https://www.mettleci.com">MettleCI</a> - DevOps for DataStage</b>
<a href="http://www.datamigrators.com/"><img src="https://www.datamigrators.com/assets/im ... l.png"></a>
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]')
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
- Participant
- Posts: 2
- Joined: Mon Nov 20, 2006 8:03 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: