Isvalid function for Date
Moderators: chulett, rschirm, roy
Isvalid function for Date
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
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,
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.
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.
Thanks in advance.
Aquilis
-
- Premium Member
- Posts: 99
- Joined: Mon Sep 03, 2007 7:49 am
- Location: Stockholm, Sweden
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'
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
http://it.toolbox.com/blogs/bi-aj
my blog on delivering business intelligence using agile principles
-
- Premium Member
- Posts: 99
- Joined: Mon Sep 03, 2007 7:49 am
- Location: Stockholm, Sweden
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'
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
http://it.toolbox.com/blogs/bi-aj
my blog on delivering business intelligence using agile principles