Re: Dates and NULL's`

>>> Where mydate>= "2011/04/01"::date and mydate<= "2011/04/30"::date
>>> With the above where will the NULL's be selected????
>>
>> Here is what I get when I try:
>>
>> spi=>  SELECT NULL::DATE>= '2011-04-01'::DATE AND NULL::DATE<=
>> '2011-04-30'::DATE;
>>   ?column?
>> ----------
>>
>> (1 row)
>>
>>
>> spi=>  SELECT (NULL::DATE>= '2011-04-01'::DATE AND NULL::DATE<=
>> '2011-04-30'::DATE) IS TRUE;
>>   ?column?
>> ----------
>>   f
>> (1 row)
>>
>>
>> spi=>  SELECT (NULL::DATE>= '2011-04-01'::DATE AND NULL::DATE<=
>> '2011-04-30'::DATE) IS FALSE;
>>   ?column?
>> ----------
>>   f
>> (1 row)
>>
>>
>> spi=>  SELECT (NULL::DATE>= '2011-04-01'::DATE AND NULL::DATE<=
>> '2011-04-30'::DATE) IS UNKNOWN;
>>   ?column?
>> ----------
>>   t
>> (1 row)
> Sorry Richard, I do not understand.
>
> It looks like you are saying the NULLS will be returned too

As a summary:
============================================
(1)
null:date COMPARE 'real date'::DATE
Will always return NUll, so you will not get the record returned at all!

(2) examples he provided:

. (null:date COMPARE 'real date'::DATE) IS NULL;
. (null:date COMPARE 'real date'::DATE) IS UNKNOWN;

Null returns true.

(3) null is true/false;    returns false

Emi