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

asked 30 Jul '15, 08:33

Justin%20Willey's gravatar image

Justin Willey
7.6k137179249
accept rate: 20%

1

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

(30 Jul '15, 09:11) Volker Barth
Replies hidden
Comment Text Removed

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?

(30 Jul '15, 09:17) Justin Willey

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.
permanent link

answered 30 Jul '15, 09:31

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%

edited 30 Jul '15, 10:05

1

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

IMO the SET does it correctly, the docs notwithstanding.

alt text

(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:

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%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%

edited 31 Jul '15, 03:31

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×260
×30
×3

question asked: 30 Jul '15, 08:33

question was seen: 1,993 times

last updated: 31 Jul '15, 03:31