Oddity with IF and time datatypes

[justin_willey]

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

[VolkerBarth]

Do you need the (select) syntax? - I'm asking as the following simple SET statement does work (with 12.0.1.4216):

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

---

FWIW: The following aequivalent expression raises the same error when used within a query (and works within a simple SET statement):

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

[justin_willey]

That's interesting. I wrote it as a select to replicate as closely as I could where the problem originated, in a query (the variables are all actually columns in a table except shiftend). Does that mean that it is processed differently if there is no query involved?

[VolkerBarth]

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);

[VolkerBarth]

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.

[Breck_Carter]

Yeah, well, that certainly breaks The Watcom Rule, not allowing timestamp + time 🙂

IMO the SET does it correctly, the docs notwithstanding.