Under certain circumstances a calculation using the result of TIMESTAMPDIFF results in error 5070 "The given data type is not valid for the requested operation. Expected a numeric value. Field: "EXPR"":
declare @d1 date;
declare @d2 date;
set @d1 = current_date();
set @d2 = @d1;
select TIMESTAMPDIFF(SQL_TSI_DAY, @d1, @d2) / 1.5 from system.iota; -- error 5070
select TIMESTAMPDIFF(SQL_TSI_DAY, @d1, @d2) * 1.5 from system.iota; -- pass
select TIMESTAMPDIFF(SQL_TSI_DAY, '2015-01-01', @d2) / 1.5 from system.iota; --pass
select TIMESTAMPDIFF(SQL_TSI_DAY, @d1, '2015-01-01') / 1.5 from system.iota; -- pass
select TIMESTAMPDIFF(SQL_TSI_DAY, '2015-01-01', '2015-01-01') / 1.5 from system.iota; -- pass
Same error occures if I use current_date() or a database field instead of a script variable:
select TIMESTAMPDIFF(SQL_TSI_DAY, current_date(), current_date()) / 1.5 from system.iota; -- error 5070
- but -
select TIMESTAMPDIFF(SQL_TSI_DAY, current_date()+1, current_date()) / 1.5 from system.iota; -- pass
- and strange enough -
if I change "set @d2 = @d1;" to "set @d2 = @d1+1;" the error disappears (but not for +0!).
Syntax Check of ARC SQL window shows "Success" in all cases!
To circumvent this error, one can CAST the result of TIMESTAMPDIFF to SQL_DOUBLE.
Add comment