Skip site navigation (1) Skip section navigation (2)

Re: SQL question

From: Carolyn Lu Wong <carolyn(at)kss(dot)net(dot)au>
To: Philip Warner <pjw(at)rhyme(dot)com(dot)au>
Cc: "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org>
Subject: Re: SQL question
Date: 2000-07-17 03:41:38
Message-ID: 39728072.31520F2F@kss.net.au (view raw or flat)
Thread:
Lists: pgsql-sql

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

In response to

Responses

pgsql-sql by date

Next:From: Philip WarnerDate: 2000-07-17 04:11:52
Subject: Re: SQL question
Previous:From: Philip WarnerDate: 2000-07-17 02:18:56
Subject: Re: SQL question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group