[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.