date manipulation
Moderators: chulett, rschirm, roy
date manipulation
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
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
Re: date manipulation
gsym wrote: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
~Kris
Re: date manipulation
kris,
I tried with system variable @Date , still the same problem........
I tried with system variable @Date , still the same problem........
Re: date manipulation
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
~Kris
Re: date manipulation
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]
If(@DATE -
Iconv(ToTransformer.BIRTHDATE,"D/MDY") > 5840 AND @DATE - Iconv(ToTransformer.BIRTHDATE,"D/MDY") < 31025) then 1 else 0[/quote]
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)?
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)?
Last edited by gsym on Mon Aug 27, 2007 2:48 pm, edited 1 time in total.
date manipulation
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
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
satya
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.
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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.
"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.
~Kris
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: