Invalid Julian day error while comparing dates

pdntsap · Post by **pdntsap** » Fri Oct 07, 2011 1:07 pm

Hello,

A comparision is made between two date columns D1 and D2. D1 is of date datatype throughout the job but D2 is transformed and converted into a date datatype before the comparision. There are more than 500,000 rows to be compared. The date comparision gives an "Invalid Julian day" error. I believe some values in D2 are in an invalid format that is causing the comparision to fail. What is the best method of finding the values that is causing this error?

Thanks.

jwiles · Post by **jwiles** » Fri Oct 07, 2011 4:39 pm

A method I used in v7 was to check the value after the conversion. If the first byte of the value is an asterisk '*', the source was invalid. You can probably accomplish the same with IsValid(convertedDateStageVariable)

Also, assuming (hate to do that, but you didn't specify the actual date format) that your Julian Day is a string of the format YYDDD, you can perform some simple validations of that string: all numerics and the YY and DDD values are valid (1 <= DDD <= 365 or 1 <= DDD <= 366 for leap years).

Add an extra output link to a transformer and send the invalid dates to it.

Regards,

chulett · Post by **chulett** » Fri Oct 07, 2011 6:30 pm

IsValid() is expecting a string and can tell you if the characters in the string represent a valid date or not. If it does then convert it.

jwiles · Post by **jwiles** » Fri Oct 07, 2011 11:30 pm

I don't recall IsValid supporting a format other whatever than the default format string was defined as at V7.x. If the date's coming in as YYDDD then it won't pass IsValid() unless the default format is modified. Looking for the asterisks after a StringToDate() call was my work-around at the time.

chulett · Post by **chulett** » Sat Oct 08, 2011 8:09 am

Hmmm... from what I recall, IsValid() supports both a 'type' (decimal,date,etc) and a 'format' so sure thought you could pass in whatever date format string it should use for the check. Got no way to prove that other than the docs, unfortunately.

jwiles · Post by **jwiles** » Sat Oct 08, 2011 9:26 pm

'fraid not...even 8.1 doesn't support a format string in IsValid. The doc is misleading. It says "format" where it should say "string".

The drawback to using the conversion results is warning messages when attempting to convert non-numeric julian days, but that's what message handlers are for

regards,

pdntsap · Post by **pdntsap** » Mon Oct 10, 2011 4:22 pm

Thanks guys.
I made some changes to the business logic and the comparision of dates seems to be working fine now. I will mark the post as resolved.

Thanks.