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.