query using a date field that isn't set - Mailing list pgsql-sql

From John B. Scalia
Subject query using a date field that isn't set
Date
Msg-id 0I6K008ES6EI0H@chimmx04.algx.net
Whole thread Raw
Responses Re: query using a date field that isn't set
List pgsql-sql
<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Hi all,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">I’ve got a SQL problem that’s stumping me. I have an employee table with a field
labeled:</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Emp_terminated            DATE</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Obviously, some employees in this table won’t have this field set. I want a query to select only
employeeswithout this field being set, ie., only current employees. I’ve got the inverse working by
using:</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">SELECT * FROM employee WHERE ISFINITE(emp_terminated);</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt;
 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">And that gives me all the old employees (where the field has a value), but for the life of me, I
can’tfigure out the syntax to use for employees that haven’t been terminated or quit. Everything I’ve tried returns no
rows.</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Anybody?</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">--</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">John</span></font></div>

pgsql-sql by date:

Previous
From: Mischa Sandberg
Date:
Subject: Re: Join Table
Next
From: jcelko212@earthlink.net (--CELKO--)
Date:
Subject: third edition of SQL FOR SMARTIES