Re: SQL question

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 table1where start_date_time::date >= '01/01/2000'::dateand start_date_time::date <= '01/01/2001'::dateand
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   |/