Fatal Error: Invalid Julian day in Filter Stage

[dougcl]

Hi folks, Peek stage shows this data:

Code: Select all

RECORDED_TIME:2008-09-23 11:36:00 EFFECTIVE_TIME:2008-09-20 03:14:00 TO_TIME:2008-09-20 14:10:00

Filter stage with this where clause:

Code: Select all

RECORDED_TIME BETWEEN EFFECTIVE_TIME AND TO_TIME

or with this where clause:

Code: Select all

RECORDED_TIME >= EFFECTIVE_TIME AND RECORDED_TIME < TO_TIME

either way results in this error:

Code: Select all

Fatal Error: Invalid Julian day

What am I doing wrong? Does this comparison require a date datatype instead of a timestamp datatype?

Thanks,
Doug

[Sreenivasulu]

You can use the isValid function to validate whether the input date is in the correct format.

Regards
Sreeni

[ArndW]

What is the datatype of the EFFECTIVE_TIME and TO_TIME columns? Can you convert these explicitly to timestamp before the filter stage?

[dougcl]

What is the datatype of the EFFECTIVE_TIME and TO_TIME columns? Can you convert these explicitly to timestamp before the filter stage?

DataStage timestamp. All three are DS timestamp.

[ArndW]

I just put together a quickie job, Row Generator with 2 timestamp colums and put them through a filter without any errors or warnings.
If you add $OSH_PRINT_SCHEMAS to your job and look at the actual schemas going into your filter stage are they actually timestamp?

[dougcl]

add $OSH_PRINT_SCHEMAS to your job and look at the actual schemas going into your filter stage are they actually timestamp?

Code: Select all

RECORDED_TIME: nullable timestamp;
EFFECTIVE_TIME: timestamp;
TO_TIME: nullable timestamp;

Doing the obvious and removing "nullable" led to a null error, so I think that must be the problem. I am getting a null in there, which is obviously not a julian day. Thanks for your help.
Doug