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.
Invalid Julian day error while comparing dates
Moderators: chulett, rschirm, roy
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,
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,
- james wiles
All generalizations are false, including this one - Mark Twain.
All generalizations are false, including this one - Mark Twain.
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.
- james wiles
All generalizations are false, including this one - Mark Twain.
All generalizations are false, including this one - Mark Twain.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
'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,
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,
- james wiles
All generalizations are false, including this one - Mark Twain.
All generalizations are false, including this one - Mark Twain.