Adding businessdays to timestamp
Moderators: chulett, rschirm, roy
Adding businessdays to timestamp
Hi,
I have one column, value is like this 2005-07-22 12:12:12
I need to add 3 businessdays to this filed.when I add 3 days to this timestamp output shoud be 2005-07-2712:12:12
Could you please help me with this.
Thanks in advance
Sri
I have one column, value is like this 2005-07-22 12:12:12
I need to add 3 businessdays to this filed.when I add 3 days to this timestamp output shoud be 2005-07-2712:12:12
Could you please help me with this.
Thanks in advance
Sri
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
You can do this in a BASIC Transformer stage using the following derivation expression.
Otherwise you need to read the timestamp as a string, pick the pieces apart with substring techniques, add the three days, adjust the result so that the date is a valid one, then re-assemble the timestamp.
Code: Select all
Oconv(Iconv(Left(InLink.TheTimestamp,10),"DYMD")+3,"D-YMD[4,2,2]"):Right(InLink.TheTimestamp,9)
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Never assume.
The requirement may also need to exclude public holidays. Now we're necessarily talking about a table-driven solution.
And we're still talking about an incompletely specified requirement.
That said, I must plead guilty to overlooking the word "business" in the OP.
The requirement may also need to exclude public holidays. Now we're necessarily talking about a table-driven solution.
And we're still talking about an incompletely specified requirement.
That said, I must plead guilty to overlooking the word "business" in the OP.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
If you're going to work with DataStage BASIC internal format dates, then you can modify my earlier formula to take account of the fact that Mod(TheDate,7) returns 0 for Sunday and 6 for Saturday to replace the constant 3 in the addition. If it's Wednesday through Friday you need to add 5; otherwise you need to add 3.
I'm sure you can combine all this into one expression. I've kept it as three statements for clarity of exposition. I assume that the Timestamp value will never be a Saturday or Sunday; if that assumption does not hold the adjustment is one of simple arithmetic.
Code: Select all
DayNumber = Mod(Iconv(Left(InLink.TheTimestamp,10),"DYMD"),7)
N = (If DayNumber <= 2 Then 3 Else 5)
Oconv(Iconv(Left(InLink.TheTimestamp,10),"DYMD")+N,"D-YMD[4,2,2]"):Right(InLink.TheTimestamp,9)
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
The point about using the BASIC Transformer stage is that the regular parallel Transformer stage does not have Iconv and Oconv available.
Without these it's a really ugly problem; you will have to write your own function. As I said earlier, you need to read the timestamp as a string, pick the pieces apart with substring techniques, add the three (or five, depending on the weekday) days, adjust the result so that the date is a valid one (for example not 30 Feb), then re-assemble the timestamp.
Without these it's a really ugly problem; you will have to write your own function. As I said earlier, you need to read the timestamp as a string, pick the pieces apart with substring techniques, add the three (or five, depending on the weekday) days, adjust the result so that the date is a valid one (for example not 30 Feb), then re-assemble the timestamp.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.