Received: from kss.net.au ([203.41.122.41])
	by hub.org (8.10.1/8.10.1) with ESMTP id e6H3bx032138
	for <pgsql-sql@postgresql.org>; Sun, 16 Jul 2000 23:38:00 -0400 (EDT)
Received: from kss.net.au (coco [10.10.1.9])
	by kss.net.au (8.8.7/8.8.7) with ESMTP id NAA22759;
	Mon, 17 Jul 2000 13:40:17 +1000
Message-ID: <39728072.31520F2F@kss.net.au>
Date: Mon, 17 Jul 2000 13:41:38 +1000
From: Carolyn Lu Wong <carolyn@kss.net.au>
X-Mailer: Mozilla 4.7 [en] (WinNT; I)
X-Accept-Language: en
MIME-Version: 1.0
To: Philip Warner <pjw@rhyme.com.au>
CC: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Subject: Re: SQL question
References: <3.0.5.32.20000717121856.022cd100@mail.rhyme.com.au>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Archive-Number: 200007/163



Philip Warner wrote:
> 
> At 12:07 17/07/00 +1000, Carolyn Lu Wong wrote:
> >I have table with the following definition:
> >
> >       create table table1(
> >               account_no      int4,
> >               start_date_tme  datetime
> >               ....
> >       );
> >
> >The table may contain null values for start_date_time.
> >
> >When I run the following SQL query, it fails:
> >
> >       select * from table1
> >       where start_date_time::date >= '01/01/2000'::date
> >       and start_date_time::date <= '01/01/2001'::date;
> >
> >I get error message 'Unable to convert null datetime to date.
> >
> >It's fine if I run the same SQL query with added condition as follows:
> >
> >       select * from table1
> >>      where account_no = 1
> >       and start_date_time::date >= '01/01/2000'::date
> >       and start_date_time::date <= '01/01/2001'::date;
> >
> >Is this a bug? Or there's an logical explaination for this?
> 
> The most logical explanation is that there are no null values in
> start_date_time when account_no = 1.
> 
> Try
>     select count(*) from table1 where account_no = 1 and start_date_time is
> null;
> 
> and see if you get 0.

Yes, i get 0 from running the above query, but it fails if i re-arrange
the where clause to:

	select * from table1
	where start_date_time::date >= '01/01/2000'::date
	and start_date_time::date <= '01/01/2001'::date
	and account_no = 1;

with the same error message.

> 
> ----------------------------------------------------------------
> Philip Warner                    |     __---_____
> Albatross Consulting Pty. Ltd.   |----/       -  \
> (A.C.N. 008 659 498)             |          /(@)   ______---_
> Tel: (+61) 0500 83 82 81         |                 _________  \
> Fax: (+61) 0500 83 82 82         |                 ___________ |
> Http://www.rhyme.com.au          |                /           \|
>                                  |    --________--
> PGP key available upon request,  |  /
> and from pgp5.ai.mit.edu:11371   |/