SAP SQL Anywhere

Question

This:

create variable timefrom time;
create variable timeto time;
create variable shiftdate date;
create variable shiftend datetime;
set timefrom = '09:00';
set timeto = '11:00';
set shiftdate = '2015-08-01';
set shiftend = (select (if timefrom < timeto then shiftdate else dateadd(day,1,shiftdate)  endif)+ timeto);

gives the error "Cannot convert time to a numeric"

wrapping the if expression in a date() function solves the problem, suggesting that the if expression is returning a numeric representation of the date rather than a date datetype.

same behaviour in v16.0.1.2018 & 10.0.1

Answer 1

The reason seems to be that the engine fails to calculate the result type of the addition of a time to a datetime, cf. that exprtype:

select exprtype('select shiftend + timeto', 1);

It returns the same error.

In contrast, the addition of a date and a time returns a timestamp/datetime, as expected:

select exprtype('select shiftdate + timeto', 1);

Additionally, the following doubled time portion addition fails, as well:

select exprtype('select shiftdate + timeto + timeto', 1);

However, the engine seems to be able to calculate the addition itself, as the SET statement apparently works ~~as expected~~ somewhat surprisingly, given the following:

The 12.0.1 docs seem to explain that behaviour here - and do not list "timestamp + time":

The following arithmetic operators are allowed on dates:

    timestamp + integer   Add the specified number of days to a date or timestamp.
    timestamp - integer   Subtract the specified number of days from a date or timestamp.
    date - date   Compute the number of days between two dates or timestamps.
    date + time   Create a timestamp combining the given date and time.

Answer 2

1

Given the Watcom date + time addition rule, I'd think the solution is as following:

As dateadd() returns a datetime but you know that adding a day to a date will still be a date, you could cast the dateadd result explicitly to a date:

set shiftend =
    (select (if timefrom < timeto then shiftdate else cast(dateadd(day,1,shiftdate) as date) endif) + timeto);

permanent link

answered 31 Jul '15, 03:29

Volker Barth
39.8k●358●546●815
accept rate: 34%

edited 31 Jul '15, 03:31

flat view

SAP SQL Anywhere

Oddity with IF and time datatypes

Follow this question

Related questions