DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
somu_june



Group memberships:
Premium Members

Joined: 14 Sep 2005
Posts: 439
Location: 36p,reading road
Points: 5111

Post Posted: Sat Jan 29, 2011 9:11 am Reply with quote    Back to top    

DataStage® Release: 8x
Job Type: Parallel
OS: Unix
Hi,

I have a job in which I have a date field which is varchar(10) and I'm getting date record in different formats like mm/dd/yyyy, m/d/yyyy, mm/d/yyyy and m/dd/yyyy example 10/10/2010, 1/1/2010, 11/1/2011 and 1/12/2011.

I want to process record only with mm/dd/yyyy format i.e 10/10/2010.

I used a function IsValid("string", DSLink2.Date[%mm/%dd/%yyyy]),

but this function returns 1 for all the above formats and I taught the function will return 1 for the format data mm/dd/yyyy and all others to 0 .

Please let me know if I'm doing something wrong.


Thanks,
Raju

_________________
somaraju
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 39021
Location: Denver, CO
Points: 199260

Post Posted: Sat Jan 29, 2011 9:18 am Reply with quote    Back to top    

They are all valid strings, in spite of your (I believe incorrect) date syntax. I would think you'll need to approach this differently: length = 10, count of "/" = 2, one at position 3 and the other a ...

_________________
-craig

"The main thing is to be satisfied with your work yourself. It's useless to have an audience happy if you are not happy." -Aaron Copland
Rate this response:  
Not yet rated
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 51867
Location: Sydney, Australia
Points: 281634

Post Posted: Sat Jan 29, 2011 3:09 pm Reply with quote    Back to top    

Everything is a valid string. Why not specify "date" as the first argument of the IsValid() function, and set a default date format of "%mm/%dd/%yyyy" in the job properties just for this job?

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Sydney and Melbourne
Rate this response:  
Not yet rated
Ravi.K
Participant



Joined: 20 Nov 2010
Posts: 209
Location: Bangalore
Points: 1190

Post Posted: Sun Jan 30, 2011 10:38 am Reply with quote    Back to top    

Try with following derivation.

IsValid('date', StringToDate(InputField,"%mm/%dd/%yyyy"))

_________________
Cheers
Ravi K
Rate this response:  
Not yet rated
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 51867
Location: Sydney, Australia
Points: 281634

Post Posted: Sun Jan 30, 2011 1:28 pm Reply with quote    Back to top    

IsValid() expects a string argument. What happens when the data are not a valid date format? The StringToDate() function fails, but what result obtains?

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Sydney and Melbourne
Rate this response:  
Not yet rated
Ravi.K
Participant



Joined: 20 Nov 2010
Posts: 209
Location: Bangalore
Points: 1190

Post Posted: Sun Jan 30, 2011 11:13 pm Reply with quote    Back to top    

The derivation IsValid('date', StringToDate(InputField,"%mm/%dd/%yyyy")) yielding "1" for correct date formats and 0 for improper date formats.

Based on the return value "0" we can filter the records by considering those are improper date formats (Raju want to process the records with valid %mm/%dd/%yyyy date mask)

_________________
Cheers
Ravi K
Rate this response:  
Not yet rated
jwiles



Group memberships:
Premium Members

Joined: 14 Nov 2004
Posts: 1274

Points: 10406

Post Posted: Sun Jan 30, 2011 11:34 pm Reply with quote    Back to top    

Based on the limited examples Raju gave in his OP, it appears that the dates were generated without leading zeros in the month and day fields. If the business rules dictate to reject all records with dates that do not meet the %mm/%dd/%yyyy physical format regardless of the actual value then the IsValid/StringToDate combination should work. Keep in mind that it will also reject all dates that meet the physical format requirement but fail the valid value test (such as "02/32/2011").

StringToDate with an invalid date will return a string of asterisks when queries as a string ("**********"), which IsValid would of course see as invalid. IsValid has been enhanced with IS 8.5 to include type-specific functions (IsValidDate and so on) and IIRC can also be passed a format string for Date/Time-related datatypes.

Regards,

_________________
- james wiles


All generalizations are false, including this one - Mark Twain.
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours