Adding businessdays to timestamp

sri75 · Post by **sri75** » Fri Jul 22, 2005 12:11 pm

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

ray.wurlod · Post by **ray.wurlod** » Fri Jul 22, 2005 4:30 pm

You can do this in a BASIC Transformer stage using the following derivation expression.

Code: Select all

Oconv(Iconv(Left(InLink.TheTimestamp,10),"DYMD")+3,"D-YMD[4,2,2]"):Right(InLink.TheTimestamp,9)

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.

kumar_s · Post by **kumar_s** » Sat Jul 23, 2005 4:44 am

Hi sri,
But iam wondering, how 3 buisness day added to 22nd changed to 27th.
should saturday and sunday be excluded?

-kumar

elavenil · Post by **elavenil** » Sat Jul 23, 2005 4:59 am

Understand from his post he wants to exclude Saturday and Sunday while adding no of days to a date. If that is the case, you should check if the date falls on on these two days when you add no of days to the date.

Regards
Saravanan

ray.wurlod · Post by **ray.wurlod** » Sat Jul 23, 2005 5:13 pm

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.

sri75 · Post by **sri75** » Sat Jul 23, 2005 5:51 pm

Hi all,

Thanks for your Replies.Here I need to exclude only saturday and sunday.I am following Ray's suggested logic.Still I am working on that.
Thanks .
Sri

ray.wurlod · Post by **ray.wurlod** » Sun Jul 24, 2005 12:44 am

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.

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)

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.

sri75 · Post by **sri75** » Mon Jul 25, 2005 7:51 am

Thanks Ray
It worked.

Thanks

sri75 · Post by **sri75** » Mon Jul 25, 2005 11:39 am

HI Ray,

It worked in Basic transformer stage.How can I get the same result in normal transformer stage.
First I read the timestamp as varchar.
In the normal transformer, I coudn't use mod function or to_char function
Could you please help me.

Thanks
Sri

ray.wurlod · Post by **ray.wurlod** » Mon Jul 25, 2005 9:22 pm

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.