[firebird-support] RE: Datatypes are not comparable in expression CASE when using TIMESTAMPS
I managed to get around it as follows, but I'd say it is a bug:
What I wanted to do:
case
when ((p.StartDate <= cast('Today' as date)) and (p.EndDate >=
cast('Today' as date))) then
(cast('Today' as date) + 1)
else
p.enddate + 1
end
What I ended up doing to get around the problem:
case
when ((p.StartDate <= cast('Today' as date)) and (p.EndDate >=
cast('Today' as date))) then
(cast('Today' as date) + 1)
--else problem with dates and else section
when (p.StartDate > cast('Today' as date)) then
p.enddate + 1
when (p.EndDate < cast('Today' as date)) then
p.enddate + 1
end
[firebird-support] RE: Datatypes are not comparable in expression CASE when using TIMESTAMPS
>I managed to get around it as follows, but I'd say it is a bug:
>
>What I wanted to do:
>
>case
> when ((p.StartDate <= cast('Today' as date)) and (p.EndDate >=
> cast('Today' as date))) then
>(cast('Today' as date) + 1)
> else
> p.enddate + 1
> end
>
>What I ended up doing to get around the problem:
>
>case
> when ((p.StartDate <= cast('Today' as date)) and (p.EndDate >=
> cast('Today' as date))) then
>(cast('Today' as date) + 1)
> --else problem with dates and else section
> when (p.StartDate > cast('Today' as date)) then
> p.enddate + 1
> when (p.EndDate < cast('Today' as date)) then
> p.enddate + 1
> end
Your description sounds strange, Maya. Not that I'd say it is unreasonable that
it barks at the first statement (although Date and Timestamp are so similar
that I thought you could use them interchangeably, I've no problems running
similar queries to yours, but I hardly ever use Timestamp). If enddate is
defined as a TimeStamp and you do cast('Today' as Date), then I'd expect the
result to be Date, whereas enddate+1 would be TimeStamp (so the alternatives to
reporting an error would either be to truncate the timestamp to a date or
expand the date to a timestamp). I'd say that a better way to write your case
statement would be to either cast('Today' as TimeStamp), or alternatively
cast(p.enddate as Date) (or use CURRENT_TIMESTAMP or CURRENT_DATE, although I
think they're slightly different from 'Today'), although as said, I am slightly
surprised that you cannot use these field types interchangeably.
What surprises me more is that your first query fails, whereas the second
succeeds.
Set
[firebird-support] RE: Datatypes are not comparable in expression CASE when using TIMESTAMPS
>I managed to get around it as follows, but I'd say it is a bug:
>
>What I wanted to do:
>
>case
> when ((p.StartDate <= cast('Today' as date)) and (p.EndDate >=
> cast('Today' as date))) then
>(cast('Today' as date) + 1)
> else
> p.enddate + 1
> end
>
>What I ended up doing to get around the problem:
>
>case
> when ((p.StartDate <= cast('Today' as date)) and (p.EndDate >=
> cast('Today' as date))) then
>(cast('Today' as date) + 1)
> --else problem with dates and else section
> when (p.StartDate > cast('Today' as date)) then
> p.enddate + 1
> when (p.EndDate < cast('Today' as date)) then
> p.enddate + 1
> end
Sorry, should have sadi p.enddate is a timestamp
Set Wrote:
>What surprises me more is that your first query fails, whereas the second
>succeeds.
Yeah, I'd expect the behaviour to be the same (either work, or give an error)
whether I put the timestamp field in the "else" section or not.
