Isvalid function for Date

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
Aquilis
Participant
Posts: 204
Joined: Thu Apr 05, 2007 4:54 am
Location: Bangalore
Contact:

Isvalid function for Date

Post by Aquilis »

I am using IsValid Function for date validation, as in:

If (IsValid('Date', StringToDate(LNK_Read_Input.UPD_TS,"%yyyy%mm%dd"))=0) Then 'N' Else 'Y'

Doing this in Stagevariable1.
And mentioned main constarint as Stagevariable='Y'.
whenever i encounter with the invalid data like '20070627 0' or extra values in this field otherthan valid date format,then i am trying to move these into reject link.But its not happening and not moving data into Reject link.
Its working fine with character or alphanumeric data inplace this field.but for '20070627 0' ,its not working.I think my function in stagevariable1 is setting value as "Y" for above kind of data.

Then would be the alternate for this problem.it will work every other invalid data except extra numeric data inplace of date format('YYYYMMDD').
My source datatype is varchar(10) and targget holda as date.In the process,my aim is just to reject the invalid data format into reject link.
Any suggestions..would help me alot.

Thanks in advance.
aquilis
Aquilis
Maveric
Participant
Posts: 388
Joined: Tue Mar 13, 2007 1:28 am

Post by Maveric »

Are you getting any warning in the director saying something like "dropping record due to invalid data"?
Try
If (IsValid('Date', StringToDate(LNK_Read_Input.UPD_TS[1,8],"%yyyy%mm%dd"))=0) Then 'N' Else 'Y'
using a substring to get only the first 8 characters. Not sure if it is 1,8 or 0,7. Try both.
Aquilis
Participant
Posts: 204
Joined: Thu Apr 05, 2007 4:54 am
Location: Bangalore
Contact:

Post by Aquilis »

Maveric,
i am getting error message as :
"ORAcode -01830: date format picture ends before converting entire input string"

its oracle error message,i am using userdefined Sql query to load the adta into oracle DB.In between at transformer i am using that above mentioned function.
If (IsValid('Date', StringToDate(LNK_Read_Input.UPD_TS,"%yyyy%mm%dd"))=0) Then 'N' Else 'Y'

as I am validating the date format in the transformer it should reject and divert rejected data into rejection link.But it parsing the above function and hitting the Database even though the format is invalid.

"dropping record due to invalid data" this error message also i am not getting.
Aquilis
bikan
Premium Member
Premium Member
Posts: 128
Joined: Thu Jun 08, 2006 5:27 am

Post by bikan »

Use substring in input column
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Does your source string date contain any delimiters? Your use of VarChar(10) as a data type suggests that it does. If so, your date format picture must allow for these.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Aquilis
Participant
Posts: 204
Joined: Thu Apr 05, 2007 4:54 am
Location: Bangalore
Contact:

Post by Aquilis »

My source datatype is Varchar2(10).I may get any value either its exact correct date format value or any charcated/flag/dirty value in this column.So only i am validating the values with date fromat('YYYYMMDD').If its doesn't matches with the format so it should reject.Thats why i am using that validation constarint.So i think my validation is going wrong somewhere.could you guide me to validate incoming data with format 'YYYYMMDD'.

Thanks in advance.
Aquilis
balajisr
Charter Member
Charter Member
Posts: 785
Joined: Thu Jul 28, 2005 8:58 am

Post by balajisr »

I guess that is how StringToDate() works. It reads only characters pertaining to the format string, forms the date and ignores the rest.

Have an extra condition to check the length of the given string to be 8 in addition to IsValid check.
stefanfrost1
Premium Member
Premium Member
Posts: 99
Joined: Mon Sep 03, 2007 7:49 am
Location: Stockholm, Sweden

Post by stefanfrost1 »

Shouldn't you really do is to check if the string itself is ok, not convert it and then check it. That will fail because the date is not valid... Youre doing it in the wrong order. First check and if it is ok then you can convert it.

You need to change your format on the string ... from your format YYYYMMDD to default YYYY-MM-DD that can easily be done using standard functions.
TmpString = LNK_Read_Input.UPD_TS[1,4]:"-":LNK_Read_Input.UPD_TS[5,2]:"-":LNK_Read_Input.UPD_TS[7,2];

If (IsValid('Date', TmpString)=0) Then 'N' Else 'Y'
-------------------------------------
http://it.toolbox.com/blogs/bi-aj
my blog on delivering business intelligence using agile principles
stefanfrost1
Premium Member
Premium Member
Posts: 99
Joined: Mon Sep 03, 2007 7:49 am
Location: Stockholm, Sweden

Post by stefanfrost1 »

Shouldn't you really do is to check if the string itself is ok, not convert it and then check it. That will fail because the date is not valid... Youre doing it in the wrong order. First check and if it is ok then you can convert it.

You need to change your format on the string ... from your format YYYYMMDD to default YYYY-MM-DD that can easily be done using standard functions.

TmpString = LNK_Read_Input.UPD_TS[1,4]:"-":LNK_Read_Input.UPD_TS[5,2]:"-":LNK_Read_Input.UPD_TS[7,2];

If (IsValid('Date', TmpString)=0) Then 'N' Else 'Y'
-------------------------------------
http://it.toolbox.com/blogs/bi-aj
my blog on delivering business intelligence using agile principles
Post Reply