topic Re: Invalid date, time or timestamp value in Connectivity & Data Prep

Invalid date, time or timestamp value

Thu, 09 Jul 2015 17:18:46 GMT

I am having an issue loading data when I include a field that contains dates. I've played around with a WHERE .... BETWEEN statement and found that this fails:

But this works:

2 questions....

1. Any thoughts on how why this is failing? There are no dates between 4-30-2000 and 5-1-2000 so logically they should both work or both fail.

2. Is there a way to have the load script skip any entries where there is an "invalid date, time or timestamp value" so in the future I wouldn't have to figure what dates are breaking the query?

Re: Invalid date, time or timestamp value

sunny_talwar — Thu, 09 Jul 2015 17:22:45 GMT

To answer your second question first, you might be able to try this:

Where not IsNull(Date#(ACCTOPENDATE));

For the first one, is there a extra semi-colon that is causing it to fail may be?

Re: Invalid date, time or timestamp value

Thu, 09 Jul 2015 22:42:35 GMT

I tried the script above and got a syntax error:

Regarding question #1, I removed the semi-colon and got the same result as before.