This: create variable timefrom time; create variable timeto time; create variable shiftdate date; create variable shiftend datetime; 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 |
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:
It returns the same error. In contrast, the addition of a date and a time returns a timestamp/datetime, as expected:
Additionally, the following doubled time portion addition fails, as well:
However, the engine seems to be able to calculate the addition itself, as the SET statement apparently works 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. 1
Yeah, well, that certainly breaks The Watcom Rule, not allowing timestamp + time :) IMO the SET does it correctly, the docs notwithstanding.
(30 Jul '15, 14:49)
Breck Carter
|
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:
|
Do you need the (select) syntax? - I'm asking as the following simple SET statement does work (with 12.0.1.4216):
FWIW: The following aequivalent expression raises the same error when used within a query (and works within a simple SET statement):
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?