Subtracting 2 dates in Transformer

pkl · Post by **pkl** » Wed Oct 06, 2004 8:58 am

Hi,

I know that there have been some posts relating to date conversions. I've read every one of them but I cudn't acheive what I wanted.

I have 2 dates from different tables . In the Xfm I need to subtract one from the other.

I'm trying to do something like this.

Var1=Iconv("DSLink1.FieldName[1,10]","DYMD")
Var2=Iconv("DSLink2.FieldName[1,10]","DYMD")
Result=Var1-Var2

My Result returns '0'

Can anybody pls help me.

Thanks
Preethi

chulett · Post by **chulett** » Wed Oct 06, 2004 9:43 am

What exactly do your date fields look like?

pkl · Post by **pkl** » Wed Oct 06, 2004 10:01 am

Hi Craig,

My date field is in the Timestamp format :YYYY-MM-DD HH24:MI:SS

Thanx

chulett · Post by **chulett** » Wed Oct 06, 2004 10:10 am

Ok, that seems fine. It looks like you are using Stage Variables rather than a custom routine, is that correct? Can you cut and paste the actual derivations you are using, please and use the 'code' tags so they format nicely? It always worrys me when someone says "I'm trying to do something like this".

pkl · Post by **pkl** » Wed Oct 06, 2004 10:27 am

Hi Craig,

You are correct, I am using the Stage variables .

My code is:

StageVar1=Iconv(DSLink1.DATE[1,10],'DYMD')
StageVar2=Iconv(DSLink2.DATE2[1,10],'DYMD')
Result = StageVar1 - StageVar2

And I assign the "Result" as derivation for a column in the target.

Hope I am clear this time. Excuse me if I am not again.

I appreciate your help.

Thanks
Preethi

chulett · Post by **chulett** » Wed Oct 06, 2004 10:37 am

Not sure what to tell you as this should work fine - as long as you have data. I threw this into a scratch routine and it subtracted the two timestamps just fine. The only time I got a zero was if both were the same or if both were blank.

Any chance of that? Have you run your job through the Debugger to check what actual values are coming in with those fields?

kcbland · Post by **kcbland** » Wed Oct 06, 2004 10:39 am

Why not create a temporary output file containing the results of each of your stage variables and run it for a couple of rows?

The ICONV statement returns an integer number that equates to the number of days passed since Jan 1, 1968. So, for May 17, 1995 the number is 10000. If ICONV cannot "internalize" your date, then it returns a blank. So, if your ICONV is not handling your date format (try just "D") then it may be returning blank. So, blank - blank = blank.

pkl · Post by **pkl** » Wed Oct 06, 2004 11:50 am

Hi Ken & Craig,

I tried to debug.... My Stage Variables have '0' in them ICONV is not able to internalise the data.

I'm not sure whether I am specifying the right format string for the Iconv function.

I'm sure the data is not null or 0.

Are there any other ways to do this.

Thanks a lot .

Preethi

chulett · Post by **chulett** » Wed Oct 06, 2004 12:24 pm

Post an example (or two) of what your data actually looks like. It must not be in Timestamp format after all.

pkl · Post by **pkl** » Wed Oct 06, 2004 12:36 pm

Hi,

These are 2 sets of dates that I need to subtract
Date1=2003-08-30 00:00:00
Date2=2003-08-30 00:00:00

Date1=2003-06-30 00:00:00
Date2=2003-06-16 00:00:00

If I try to do it in the OCI it works fine if I subtract the values directly.

But I can do that only if the values are from the same table.

But now I need to subtract dates from different tables.
Then I have to do it in the Xfm.

I dont know how else I can do this.

Appreciate your help very much.

Regards
Preethi

kcbland · Post by **kcbland** » Wed Oct 06, 2004 12:46 pm

This is getting frustrating. I'm guessing you just pasted the format returned from the OCI/ODBC stage you're using, not what truly exists in the transformer at the time the math is happening. I asked you to write out the values from the transformer stage variables into a file and look at them there.

Dollars to donuts, you're selecting from either DB2 or Informix which returns dates in the INTERNAL format already. Therefore, the extra ICONV is blanking out your "date" because it's no longer a date.

Do I win the donuts?

pkl · Post by **pkl** » Wed Oct 06, 2004 1:04 pm

Hi Ken & Craig,

Thankyou very much for being so pateint with me and I'm extremely sorry for frustrating you.

It is working now.

I greatly appreciate your help.

Thanks Once again

Regards
Preethi

chulett · Post by **chulett** » Wed Oct 06, 2004 1:42 pm

So, what made it work for you? Best to post the actual solution so that others can learn from it. That and so we can settle Ken's donut dillema.

kcbland · Post by **kcbland** » Wed Oct 06, 2004 1:43 pm

What was the problem? I'm not frustrated with you, just the problem. We need to know what you were doing wrong so that everyone following this post learns from your experiences. Plus, they will see the train of thought we try to take people thru in order to troubleshoot issues.

kcbland · Post by **kcbland** » Wed Oct 06, 2004 1:44 pm

mmmm donut

My kingdom for a donut.