Thread: Need help with sql select on null dates!

I'm having trouble doing a select on empty date fields.

Suppose my table is composed of employee_number(char), hire_date(date), and
termination_date(date).
I would expect to be able to say:

    'select * from employee_tables where termination_date = null'
or:
    'select * from employee_tables where isfinite(termination_date) <>
1'

...but no luck. What is the proper way to select undefined dates?

Jeff Sacksteder wrote:
> I'm having trouble doing a select on empty date fields.
>
> Suppose my table is composed of employee_number(char),
> hire_date(date), and termination_date(date).
> I would expect to be able to say:
>
>       'select * from employee_tables where termination_date = null' or:
>       'select * from employee_tables where
> isfinite(termination_date) <>
> 1'
>
> ...but no luck. What is the proper way to select undefined dates?
>
NULLs are unknown and therefore there equality can not be tested.
select * from employee_tables where termination_date is null;
ought to work however
hth,
- Stuart

Jeff Sacksteder wrote:

>I'm having trouble doing a select on empty date fields.
>
>Suppose my table is composed of employee_number(char), hire_date(date), and
>termination_date(date).
>I would expect to be able to say:
>
>    'select * from employee_tables where termination_date = null'
>
>
Isntead of '= null' you should use the 'IS NULL' operator.
For example:

'select * from employee_tables where termination_date is null'

--
Diogo de Oliveira Biazus
diogo@ikono.com.br
Ikono Sistemas e Automação
http://www.ikono.com.br

On Tue, Nov 26, 2002 at 08:47:02 -0500,
  Jeff Sacksteder <jwsacksteder@ramprecision.com> wrote:
> I'm having trouble doing a select on empty date fields.

Can you be more precise in what you mean by "empty"? Do you mean NULL?

> Suppose my table is composed of employee_number(char), hire_date(date), and
> termination_date(date).
> I would expect to be able to say:
>
>     'select * from employee_tables where termination_date = null'
> or:
>     'select * from employee_tables where isfinite(termination_date) <>
> 1'
>
> ...but no luck. What is the proper way to select undefined dates?

Neither of the about will return rows with termination_date null in recent
versions of postgres. If the value you are trying to match is null, then
using "is null" instead of "= null".