Re: question using 'between' in a sql query - Mailing list pgsql-sql

On Sat, Dec 03, 2005 at 09:54:16AM -0800, Theodore Petrosky wrote:
> if 'between' is inclusive of the start and end dates why doesn't
> this query result in all the rows on '11/20/2005'?

EXPLAIN shows what happens when you compare a date against a timestamp:

Filter: ((insertdate >= '2005-11-20 00:00:00-08'::timestamp with time zone)    AND (insertdate <= '2005-11-20
00:00:00-08'::timestampwith time zone))
 

One way around this is to cast the timestamp column to date:

SELECT employeecode
FROM mytable
WHERE insertdate::date BETWEEN '11/20/2005' AND '11/20/2005';

-- 
Michael Fuhr