470,614 Members | 1,458 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,614 developers. It's quick & easy.

comparing dates?

My date setting is ISO with US conventions, and output from a select is in
the form yyyy-mm-dd (2002-01-18, for example.

When I do a select such as

SELECT * FROM table WHERE date BETWEEN '2001-12-28' AND '2002-01-28'

It misses the entry with date '2002-01-28' (which does exist!).

Likewise,

SELECT * FROM table WHERE date = '2001-12-28' ;

gives me '0 rows'.
The only way I've been able to handle this, so far, is in this fashion:

SELECT * FROM table WHERE
date BETWEEN 20011228 - .001 AND 20020128 + .001 ;

and similarly instead of = using, I can use

date BETWEEN 20020128 - .001 AND 20020128 + .001
I must be missing something, but I can't find it.

What is the 'right' way to select for a date type = a particular date, and
for BETWEEN to work as advertised?

I have a copy of 'Practical Postgresql', but I can't find the answer
there, or in the online manual. Of course there are a lot of places to
look and I may have missed it.

Thanks,

John Velman
Jul 19 '05 #1
1 4233
Jhon, try this
SELECT * FROM table WHERE date BETWEEN '2001-12-28'::date AND '2002-01-28'::date
Jul 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Mike N. | last post: by
2 posts views Thread by Duppypog | last post: by
2 posts views Thread by Philip Townsend | last post: by
2 posts views Thread by Manny Chohan | last post: by
2 posts views Thread by ameshkin | last post: by
12 posts views Thread by colincolehour | last post: by
4 posts views Thread by cheryl | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.