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 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 |/