Skip to Content

ADS 12 bug using the result of TIMESTAMPDIFF for a calculation

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
10|10000 characters needed characters exceeded

  • Oh my...

    I though, instead of dividing by 1.5 I can just multiply by 0.66667. But this query runs forever, resulting in 28% cpu utilization on the server! And ARC cannot cancel the query....

  • Get RSS Feed

1 Answer

  • Dec 04, 2017 at 08:31 AM

    With Service Pack 2 the error is gone.

    Good work!

    Add comment
    10|10000 characters needed characters exceeded