date manipulation

[gsym]

Hi All,
I have a problem while manipulating the date. My source and target are DB2 databases.
Job Design is
DB2 API --> TRANSFORMER -->DB2 API
-->DB2 API (reject link)

Here is the logic iam using to find whether the age is greater than 16 yrs and less than 85 yrs.

Created a stage variable DOB with this condition :
If(Iconv(DSJobStartDate,"D-YMD[4,2,2]") -
Iconv(ToTransformer.BIRTHDATE,"D/MDY") > 5840 AND Iconv(DSJobStartDate,"D-YMD[4,2,2]") - Iconv(ToTransformer.BIRTHDATE,"D/MDY") < 31025) then 1 else 0

Constraint DOB = 0 THEN TO THE REJECT TABLE.

If the age is less than 16 yrs and greater than 85 then that record should be rejected.

The records with DOB as " 01/03/1968" , 01/12/1968, 01/06/1968 are getting rejected though they are valid (greater than 16 yrs and less than 85 yrs).

Any help is appreciated.

Thanks

[kris]

Hi All,
Created a stage variable DOB with this condition :
If(Iconv(DSJobStartDate,"D-YMD[4,2,2]") -
Iconv(ToTransformer.BIRTHDATE,"D/MDY") > 5840 AND Iconv(DSJobStartDate,"D-YMD[4,2,2]") - Iconv(ToTransformer.BIRTHDATE,"D/MDY") < 31025) then 1 else 0

Constraint DOB = 0 THEN TO THE REJECT TABLE.

If the age is less than 16 yrs and greater than 85 then that record should be rejected.

The records with DOB as " 01/03/1968" , 01/12/1968, 01/06/1968 are getting rejected though they are valid (greater than 16 yrs and less than 85 yrs).

You may not want to use DSJobStartDate macro in this case.
Instead, you can use system variable @Date which is in internal format.

Try this:

If @date - Iconv(BIRTHDATE,"D/MDY") > 5840 and @date - Iconv(BIRTHDATE,"D/MDY") < 31025 then Ans=1 else Ans=0

[gsym]

kris,

I tried with system variable @Date , still the same problem........

[kris]

Did you this:

If @date - Iconv(ToTransformer.BIRTHDATE,"D/MDY") > 5840 and @date - Iconv(ToTransformer.BIRTHDATE,"D/MDY") < 31025 then Ans=1 else Ans=0

[gsym]

yes, the logic is
If(@DATE -
Iconv(ToTransformer.BIRTHDATE,"D/MDY") > 5840 AND @DATE - Iconv(ToTransformer.BIRTHDATE,"D/MDY") < 31025) then 1 else 0[/quote]

[srimitta]

What is this "5840" and "31025" ? No.of Days?

[kris]

Yes it is number of days for both 16 years and 85 years @365 days.

Gsym,

Because this logic doesn't consider all the leap years into account, it may not be giving you the accurate Age but is close though.

[gsym]

Kris,

At present iam looking for a close value for the No of days.

But, the problem is with the rejected records.

why The records with DOB as 01/03/1968, 01/12/1968, 01/06/1968 are getting rejected though they are valid (greater than 16 yrs and less than 85 yrs)?

[satya99]

Try this logic:

Create three stage variable

sv1 Iconv("01/03/1968", "D2/")
sv2 (@DATE - sv1)/365
sv3 IF sv2 > 16 AND sv2 < 85 Then @True else @False

Good luck

[ArndW]

gsym,
I think Satya99 might have the right approach - that your ICONV() isn't converting the date as expected; in which case it will return the original string and which, in turn, is causing your condition to return an unexpected result. Ensure that the ICONV() of your value is successful. I would test it with a dummy output column to a test file for checking, or to use the debugger.

[kris]

I have tried it on my system and it seems to be working fine for those dates too.

"D/MDY" seems to be handling both single digit and double digit months on my system.

But Like ArndW said, Satya99's approach of using "D2/" or "D2/MDY" would be more appropriate thing to do.

But always I would prefer to validate dates (using a simple validation routine) if they are valid before applying any calculations or transformations on them.

[ray.wurlod]

Wouldn't the job be easier to maintain if you calculated the actual ages (in stage variables, perhaps) and compared those?

[srimitta]

Better watch this post.

viewtopic.php?t=97809&highlight=calculate+age