Thread: Comparison of dates

At 01:33 AM 4/25/07, Ing. Sergio Lima wrote:

>Hi!! I am using postgresql 7.3 and I have a table with one timestamp
>without zone field (date1), I want to select any row when "date1" is
>between inicial_date and final_date values, I'm using an Sql sentence like
>this:
>
>SELECT name, date1, ......
>FROM table_name
>WHERE date1>=inicial_date and date1<=final_date
>
>it works fine if month of field "date1" is the same in "inicial_date" and
>"final_date", but if month value of "date1" is different the result is
>always NULL, any idea??????


Provide an example - works as expected on my 7.3.5 system

fbax=> create table table_name (name text, date1 timestamp);
CREATE TABLE
fbax=> \d table_name
             Table "public.table_name"
  Column |            Type             | Modifiers
--------+-----------------------------+-----------
  name   | text                        |
  date1  | timestamp without time zone |

fbax=> insert into table_name values ('Jan','2007-01-15');
INSERT 18307875 1
fbax=> insert into table_name values ('Feb','2007-02-25');
INSERT 18307876 1
fbax=> insert into table_name values ('Mar','2007-03-23');
INSERT 18307877 1
fbax=> insert into table_name values ('Apr','2007-04-11');
INSERT 18307878 1
fbax=> select name,date1 from table_name where date1>='2007-01-29' and
date1<='2007-04-10';
  name |        date1
------+---------------------
  Feb  | 2007-02-25 00:00:00
  Mar  | 2007-03-23 00:00:00
(2 rows)

fbax=> select name,date1 from table_name where date1 between '2007-02-25'
and '2007-04-11';
  name |        date1
------+---------------------
  Feb  | 2007-02-25 00:00:00
  Mar  | 2007-03-23 00:00:00
  Apr  | 2007-04-11 00:00:00
(3 rows)




My guess is that perhaps you are not getting rows where date1::date =
final_date in your results.  If that is the case, you should try:

SELECT name, date1, ...... FROM table_name
WHERE date1::date >= inicial_date and date1::date <=final_date