IsValid() problems with non-standard dates

[ArndW]

I have a VarChar(7) column containing a date in YYYYddd format, i.e. "2008234" which can be parsed using StringToDate(In.Col,"%yyyy%ddd"). Unfortunately, there are rows that contain invalid values.

According to the documentation, the derivation

Code: Select all

IsValid('date [%yyyy%ddd]',In.Col)

should return an integer true or false, but unfortunately at runtime I get an error message

APT_CombinedOperatorController,0: The schema type "date" does not take parameters "%yyyy%ddd"

I've played with variations on this theme with no luck, and have searched both the DataStage PDFs as well as the orchestrate documentation to no avail.

IsValid('date ["%yyyy%ddd"]',In.Col)
IsValid('date "[%yyyy%ddd]"',In.Col)
IsValid('date %yyyy%ddd',In.Col)
IsValid('date "%yyyy%ddd"',In.Col)

[trammohan]

First convert the string to yyyy-mm-dd format and use Isvalid("date",string)... returns 1 or 0

[trammohan]

First convert the string to yyyy-mm-dd format and use Isvalid("date",string)... returns 1 or 0

[ArndW]

Umm, that defeats the whole purpose of using IsValid() in the first place - if you can successfully convert the string to a date then IsValid() will always be true. And if the string is an invalid format, then you get the error message that you are using IsValid() to avoid in the first place.

[trammohan]

Dates can be like this 2000-00-00 ....Isvalid is expeting in this yyyy-mm-dd format........

[ArndW]

No, in my case the dates are formated as strings like "2008032" (for Feb 1, 2008) and therefore I need to use a non-default date format for the IsValid() function and it seems that the functionality is supported, but nobody seems to know what the correct syntax is for the first parameter.

[trammohan]

Try this

datestr="2008002"

IsValid("date",StringToDate(datestr,"%yyyy%ddd"))

first parameter is "date"

[ArndW]

trammohan - of course that works. Now use "2008400" or "200805". The whole point is to avoid errors and warning in the StringToDate() function by using IsValid().

[trammohan]

Hi,

First if the input str length <> 7 ... throw an exception else IsValid () works fine.....

[ray.wurlod]

How about assuming that the square brackets mean "optional" and that double-quote characters are needed for the first argument?

Code: Select all

IsValid("date %yyyy%ddd",In.Col) 

[ArndW]

Trammohan = "2007400" has a length of 7, so does "2x0x4x0".

Ray - I tried that as well yesterday, the runtime message states

Parsing parameters for schema type "date": Expected "[" or EOF; got: "%"

", so all of my further tests had the open square bracket.

[ray.wurlod]

Fair enough - I'm at a server-only site at the moment, so couldn't check, but you hadn't mentioned it.

There is no mention in Appendix A of Parallel Job Developer's Guide (Functions) about a square-bracketed format string being available with the IsValid() function - and this is the only reference to the function in this manual (I searched) - where did you encounter it?

[ArndW]

I don't recall where I got that syntax from, since the Developer's Guide description of IsValid() only hints at a format string. It is nasty that neither the docs nor the Orchestrate manuals contain any description on how to do this. I would normally change the default date format for the job, but we actually have 3 different date formats in the same input file so that won't work. I've got a call open with support, but they haven't even gotten past the "please send us a sample job" phase so I'm not too germane about the chances of a quick resolution - my best hope was the pooled expertise in DSXchange.

[ray.wurlod]

Perhaps you could use a BASIC Transformer stage with this IsValid() function !

[ArndW]

I might have to, but with a requirement of 500 rows per second (very big rows) it might not have the oomph necessary and certainly won't scale. I might need to play around with string conversion and manual checking for this column; but I'm still hoping that the solution is getting a simple syntax from Support (I know, I'm still a naive optimist, even after so many years)