DSXchange

Hi,

I have a file with the below values in one of the date field:
Field_Name : Post_DT

10:00:09
1111:11
14:54:37
09:08:07
20170812

The date column will have both time and date, if I get time then it has to be replaced with current_date while loading into target table and date will be as such, so have wrote this below constraints in Transformer.
Since I'm getting all values in char format in source file, have used stringtodate and stringtotime, The above condition works fine, but I'm getting a warning "Data string was not matching with the format yyyymmdd" for the first 4 values. I believe while checking the date valid condition even though its not matching it throws an warning and moves to the next "if".

had the output like this in table,
2017-08-23
2017-08-23
2017-08-23
2017-08-23
2017-08-12

Can somebody help me to get rid of this warning message?

Thanks..

Well... the first thing that jumps out at me is the conversion you are doing inside the IsValid call:
The test is to see if it can be converted successfully. The function is expecting a string and the warning should be gone if you test the string and then conditionally convert it only if it passes the test. There's a Technote here on the issue.

Thanks, Have changed the code like below still i'm getting the warning message.

If len(Post_DT) = 8 then
If IsValid('Date', Post_Dt[1,4]:Post_Dt[5,2]:Post_Dt[7,2]) = 1 then
StringtoDate(Post_Dt, "%yyyy%mm%dd%)
Else If IsValid('Time', Post_Dt[1,2]:":":Post_Dt[4,2]:":":Post_Dt[7,2]) = 1 then
Current_Date()
Else SetNull()
Else SetNull()

i believe that braces needed to change for each IF statement, let me try this, if in case you have any solutions, please pass on. Thanks..

You need to concentrate on the correct syntax for the IsValid() function or, perhaps, use functions such as IsValidDate() and IsValidTime().

The approach I would use is something like the following:

Just wanted to highlight something Ray didn't specifically call out on his IsValid example...

IsValid returns a Boolean value of "0" or "1". In DataStage, False is represented by a value of "0" and True is represented by a value of "1".

That's why Ray eliminated the " = 1" portion of your formula. The "If IsValid()" syntax will automatically execute the "Then" clause when the test passes, or take the "Else" clause when the test fails because the test is returning "True" or "False" as a result.

Also note that I made no attempt to provide a complete solution, just wanted to give you some insight into the 'why' of your error message.