Error : Invalid operation for DateTime or Interval.

Analytics
Enthusiast

Error : Invalid operation for DateTime or Interval.

[Yes, I looked at the other posts on this error message; unfortunately, they're getting there on a different path]

 

My query generates "[Teradata][ODBC Teradata Driver][Teradata Database](-5407)Invalid operation for DateTime or Interval." Basically, it's a ridiculously long SELECT with literal values (test data), i.e. no columns. Ridiculously long because of the infrastructure around it, which doesn't provide any way to set SQL variables, so there are lots of repeats of the same subexpressions. I can post the SQL if it will help.

 

Basically, the expression is (%1 + (%2 - TRUNC(%1, 'DD'))),where %1 is a date literal like DATE '2013-12-30', (though it might also be a TIMESTAMP in the real world) and %2 is the ridiculously long expression. %2 is of the form TRUNC(%3, 'IW'), where %3 may be hundreds or thousands of characters.

 

The %3 subexpression has no issues; I can return it from the function instead of the correct value, and no one is upset except the test harness. But wrapping the rest of the first expression around it makes Teradata sad. OK, maybe it makes *me* sad.

 

So, obviously, I'm missing the target on how to add the difference between two DATE values to a DATE or TIMESTAMP value.

 

Anyone have suggestions on how to correct my calculation?

7 REPLIES 7
Teradata Employee

Re: Error : Invalid operation for DateTime or Interval.

Assuming the TRUNC functions are returning DATE values, the difference will be an integer (number of days).

You should be able to add that to another DATE value with no problem (and again have it treated as number of days), but you would need to convert it to an INTERVAL to add it to a TIMESTAMP literal.

(%1 + (INTERVAL '1' DAY)*(%2 - TRUNC(%1, 'DD'))),

Ambassador

Re: Error : Invalid operation for DateTime or Interval.

Fred's suggestion will work unless there difference is over 9999 days.

But what are you actually trying to calculate?

For the date part this seems to be a complicated calculation to return the Monday previous to %3, which is already done by Trunc(d3, 'IW').

Do you want to retain the time portion?

 

Basically, it's a ridiculously long SELECT with literal values (test data), i.e. no columns. Ridiculously long because of the infrastructure around it, which doesn't provide any way to set SQL variables, so there are lots of repeats of the same subexpressions.

 

Well, you can alias the literal value and use the alias instead of the base calculation:

SELECT DATE '2013-12-30' AS d1,
   Current_Date AS d3,
   (d1 + (Trunc(d3, 'IW') - Trunc(d1, 'DD'))) AS new_d,
new_d + 1 AS another_

Do you need those variables for a single query or within a session or globally?

How are they applied?

 

I can post the SQL if it will help.

Might be helpful :-)

 

 

 

 

Enthusiast

Re: Error : Invalid operation for DateTime or Interval.

Well, crap. Even with explicit casting

   (%1 + (INTERVAL '1' DAY)*(CAST(%2 AS DATE) - CAST(TRUNC(%1, 'DD') AS DATE)))

, it gives the same error if %1 is a TIMESTAMP. Works fine if %1 is a DATE:

 

Works: SELECT (DATE '2016-05-24' + (INTERVAL '1' DAY)*(CAST(....

Fails: SELECT (TIMESTAMP '2016-12-28 12:34:56' + (INTERVAL '1' DAY)*(CAST(....

 

"But what are you actually trying to calculate?

For the date part this seems to be a complicated calculation to return the Monday previous to %3, which is already done by Trunc(d3, 'IW').

Do you want to retain the time portion?"

 

Exactly.

 

I'm implementing DATEADD('iso-quarter' (or 'iso-year'), count, start)

[yes, I know; I also think ISO 8601 week-based calendar quarter is a bit bogus, but it wasn't my call....]

 

If 'start' is a DATE, %2 already has that value. But, if 'start' is a TIMESTAMP, the additional expression adjusts it by the number-of-days difference between the calculated new date and 'start', thus preserving the time portion. Theoretically.

 

Also, I have no idea what the actual columns in our customer's DB might be: DATE, TIMESTAMP(0), TIMESTAMP(6), whatever.

 

I've seen multiple comments from various Web searches to the effect that "Teradata is really picky when doing things with TIMESTAMPs and INTERVALs"; I'm beginning to think that a generic solution that preserves the time might not be worth the effort....

Ambassador

Re: Error : Invalid operation for DateTime or Interval.

What is your definition of adding an ISO-quarter or an ISO-year?

Is it different from simply adding a 3 months or 12 months?

 

Can you show some example timestamps and the expected results?

 

 

Enthusiast

Re: Error : Invalid operation for DateTime or Interval.

Since the ISO 8601 week-based calendar is based on...weeks...year/quarter addition is done by offsetting the day-in-year or day-in-quarter by the specified amount. Because some years have 53 weeks instead of 52, that can result in an overflow condition, where adding 'year-count' to a date in Gregorian year NNNN, which may or may not be in ISO year NNNN, produces a result that is not in Gregorian year (NNNN+year count). Similarly for quarters, where the anchor is the day-in-quarter of the starting point, so day-in-quarter of (start + quarter-count) may not end up in the same ISO quarter (or even the same ISO year) as would result from doing the same operation with Gregorian dates.

 

Examples:

// Last week of 53-week year (Greg = ISO) -> 52-week year
DATEADD('iso-quarter', 4, #2015-12-29#) // #2017-01-03#
DATEADD('iso-quarter', -4, #2015-12-29#) // #2014-12-30#

// Last week of 53-week year (Greg > ISO) -> 52-week year
DATEADD('iso-quarter', 4, #2016-01-02#) // #2017-01-07#
DATEADD('iso-quarter', -4, #2016-01-02#) // #2015-01-03#

And the one that doesn't work:

// Verify that time component is retained
DATEADD('iso-quarter', 4, #2016-12-28 12:34:56#) // #2017-12-27 12:34:56#

 

Ambassador

Re: Error : Invalid operation for DateTime or Interval.

Do you also use those strange ISO months from the Businesscalendar, i.e. a month starts always on a Monday?

Btw, this is definitely not based on ISO, only the year/week numbers, and I never met anyone who claimed to know why this should be useful, you would be the first one :-)

 

How will you to deal with week 53? There must be two different dates (a week apart) where adding an "ISO year" results in the same date, e.g. adding one "ISO year" to both '2015-12-29' and '2016-01-05' will result in '2017-01-03'?

Do you have exact rules describing this logic?

Why are there two date/timestamps in your formula?

 

 

Enthusiast

Re: Error : Invalid operation for DateTime or Interval.

No; fortunately, someone kept screaming "ISO months don't exist!" "There's no such thing as an ISO month!" often enough and loud enough that they were not considered.

OK, "someone" might have been me....

So that 445/454/544/oops-it's-a-53-week-year-so-it's-545 nonsense never came up.

 

"There must be two different dates (a week apart)...."

Yep. Adding X ISO years to  day N of week 53 of ISO year Y and adding X-1 years to day N of week 1 of ISO year Y+1 (which is always a 52-week year; 53-week years are never closer together than 6 years) both end up at the same place.

 

These overflow rules about adding years or quarters to week-53 dates are ours; AFAIK, there are no 'official' rules on how to handle this situation. The alternative would be to truncate, but you'd have the mirror issue where two different additions end up at the same day in week 52.

 

Not sure what you mean about 2 values; the one inside is the parameter, and the one after the // comment marker is the expected return value.