IsValid using Date

[ggarze]

What am I doing wrong.

I first did this and it worked fine unless I encountered an invalid date like '999999' StringToDate(stgAcctDate, '%1950yy%mm%dd')
So, then I added this to take care of that: if IsValid('Date', stgAcctDate) then StringToDate(stgAcctDate, '%1950yy%mm%dd') else SetNull(). Everything outputed was then being passed as NULL, even valid dates. So, then I asked myself how does DataStage know 'Date' is in format 'YYMMDD' like I'm passing it.

So, then I did this: if IsValid('Date', stgAcctDate['%yy%mm%dd']) then StringToDate(stgAcctDate, '%1950yy%mm%dd') else SetNull(). Still every record being outputed as NULL

So, then I put in a valid date: if IsValid('Date', '100401'['%yy%mm%dd']) then StringToDate(stgAcctDate, '%1950yy%mm%dd') else SetNull(). Still every record being outputted as NULL.


What am I doing wrong.
Thanks,
Glenn

[ray.wurlod]

If you use the expression builder to build your expression you will get the syntax for IsValid() function as having two or three arguments - the third argument is a date format string. Further, input column names need to be qualified with the link name, which also happens automatically if you use the expression editor.

Code: Select all

IsValid("Date", InLink.stgAcctDate, "%yy%mm%dd")

There is also an IsValidDate() function - at least in version 8.5 - which expects a date string as its single argument.

[ggarze]

I was hoping that was the issue but doesn't seem to be as I used the expression editor to bring in IsValid and I got the following

IsValid(%typestring%,%valuestring%)

I even tried adding the 3rd argument but the text stays red and when I validate it says too many argumnets in expression.

[Ravi.K]

Try in this angle:

IsValid('Date',StringToDate(stgAcctDate, '%1950yy%mm%dd'))

For valid dates it returns 1 and invalid dates it returns 0.

[csrazdan]

Are you writing this code in a Stage Variable? Looks like that since I see the Stage Variable name in your code and not link name. I am surprised you can even able to compile this code on Windows. We had a similar code and job would not even compile on AIX in 8.x environment though it would run fine in 7.5.x environment. Following is the response which I have from IBM support:

The problem here is that Stage variables do not have a "nullable" option
as DataStage considers them to be non-nullable, thus the usage of
SetNull() for stage variables is not supported. IIS 8.1 traps this
condition and will report it as an error. DataStage 7.5 did not trap
this condition at compile time which means that the job had potential
to fail at runtime if a null actually did get assigned.
.
The capability to support SetNull() for stage variables was under
consideration for IIS 8.5, but not for earlier releases. Thus for IIS
8.1 the recommendation is to set the field to '' (2 single quotes)
rather than using SetNull() function.

[Sreenivasulu]

I think this
--
StringToDate(stgAcctDate, '%1950yy%mm%dd') else SetNull()
--

should be this

StringToDate(stgAcctDate, '%19yy%mm%dd') else SetNull()

[ggarze]

I'll try the latest two examples and let you guys know.

regarding am I writing to a stage variable, no. You are correct in that you can't write a null to a stag variable. This code is simply using a stage variable in the calc to write to an output. In the stage variables I check for Null first, if it is I move '999999' to the field which would force an invalid date, making the output null

[ggarze]

so it looks like IsValid('Date',StringToDate(stgAcctDate, '%1950yy%mm%dd')) worked

I just get the annoying warning: Conversion error calling conversion routine date_from_string data may have been lost: which I'll have to ignore in the message handler for each date that I have in the transformmer. Other then that the date or NULL gets populated in the table.

thanks for your help