Thread: how to cast for dates/datetimes?

how do i get around this?

select sum(total) from lineitemsnext where date <= ('now'::date + '1
month'::timespan);

ERROR:  Unable to identify an operator '<=' for types 'date' and
'datetime'
        You will have to retype this query using an explicit cast



-----------------------------------------------------------------------------
david@backpack.com            BackPack Software, Inc.        www.backpack.com
+1 651.645.7550 voice       "Life is an Adventure.
+1 651.645.9798 fax            Don't forget your BackPack!"
-----------------------------------------------------------------------------

Dave Pfaltzgraff@PATAPSCO
08/22/2000 08:03 AM

D. Ducnini asks:
>how do i get around this?
>
>select sum(total) from lineitemsnext where date <= ('now'::date + '1
>month'::timespan);
>
>ERROR:  Unable to identify an operator '<=' for types 'date' and
>'datetime'
>        You will have to retype this query using an explicit cast

I have not used this kind of select to find a SUM, but when I delete old data
from a log, I use:
DELETE FROM Log WHERE LG_Time < 'today'::timestamp - '1 month'::timespan;

Is your problem occuring because you're mixing a 'date' and a 'timestamp'?

> I suspect that your calculation yeilds a timestamp instead of date because
> 'now' is a timestamp, not a date, try:
>
> select sum(total) from lineitemsnext where date <= ('now'::date + '1
> month'::timespan)::date;

This one did the trick.  I'll try the other variants later on.

Thanks!

-duck


-----------------------------------------------------------------------------
david@backpack.com            BackPack Software, Inc.        www.backpack.com
+1 651.645.7550 voice       "Life is an Adventure.
+1 651.645.9798 fax            Don't forget your BackPack!"
-----------------------------------------------------------------------------