Warning from Date and time check in Transformer

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
Mohan09
Participant
Posts: 4
Joined: Wed Jul 18, 2018 8:57 pm

Warning from Date and time check in Transformer

Post by Mohan09 »

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.

Code: Select all

If len(Post_DT) = 8 then
 If IsValid('Date', StringtoDate(Post_Dt, "%yyyymmdd%")) = 1 then
   StringtoDate(Post_Dt, "%yyyymmdd%)
  Else If  IsValid('Time', StringtoTime(Post_Dt, "%hh:nn:ss%")) = 1 then
    Current_Date()
    Else SetNull()
        Else SetNull()
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..
Regards,
Mohan
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Well... the first thing that jumps out at me is the conversion you are doing inside the IsValid call:

Code: Select all

If IsValid('Date', StringtoDate(Post_Dt, "%yyyymmdd%")) = 1 then
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.
-craig

"You can never have too many knives" -- Logan Nine Fingers
Mohan09
Participant
Posts: 4
Joined: Wed Jul 18, 2018 8:57 pm

Post by Mohan09 »

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..
Regards,
Mohan
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

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:

Code: Select all

If Len(inLink.TheString) = 8 Then If IsValid("Date", inLink.TheString) Then StringToDate(inLinkTheString) Else If IsValid("Time", inLink.TheString) Then CurrentDate() Else SetNull() Else SetNull()
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
asorrell
Posts: 1707
Joined: Fri Apr 04, 2003 2:00 pm
Location: Colleyville, Texas

Post by asorrell »

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.
Andy Sorrell
Certified DataStage Consultant
IBM Analytics Champion 2009 - 2020
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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.
-craig

"You can never have too many knives" -- Logan Nine Fingers
Post Reply