From: Carolyn Lu Wong <carolyn@kss.net.au>
To: Philip Warner <pjw@rhyme.com.au>
Cc: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: SQL question
Date: Mon, 17 Jul 2000 13:41:38 +1000
Message-ID: <39728072.31520F2F@kss.net.au> (raw)
References: <3.0.5.32.20000717121856.022cd100@mail.rhyme.com.au>
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 |/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: carolyn@kss.net.au, pjw@rhyme.com.au
Subject: Re: SQL question
In-Reply-To: <39728072.31520F2F@kss.net.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox