From: ccremid@saadev.saa.noaa.gov (Constantino Cremidis)
Subject: Re: Help!  SQL example needed for date comparison!
Date: 1997/12/12
Message-ID: <66s449$cj4@cssun.mathcs.emory.edu>#1/1
Distribution: world
Sender: walt@mathcs.emory.edu
X-Informix-List-ID: <list.17845>
Organization: SAA/NOAA
Reply-To: ccremid@saadev.saa.noaa.gov (Constantino Cremidis)
Newsgroups: comp.databases.informix



Gilbert R. Payson wrote:
} 
} An example for sybase would be:
} 
} select * from telephone_book where datediff(dd,my_datetime,getdate()) = 1
} 
} : >ah262@detroit.freenet.org wrote:
} : >>
} : >> I am in urgent need of some SQL sample code that takes a database
} : >> DATETIME formatted field and compares it to the system date (minus one
} : >> day) for equality.  I am, in effect, attempting to pull the records from
} : >> a table dated 1 day previous from the current system date.  Sounds
} : >> simple, eh? I've looked at using the various date functions that Informix
} : >> provides, but each one is very specialized in the resulting value it
} : >> produces.
} : >
} : >I do not understand:
} : > select * from table
} : >        where table.datecolumn=today-1
} : >does not work ?

if table T1 has column COL1 defined as datetime year to second
then

select * from t1
where date(col1) = TODAY - 1

should do what you are looking for


} : >
} : >Michal
} : >
} : >--
} : >--------------------------------------------------------------
} : >Michal Hajek                   mailto:hajek@nspuh.cz
} : >Sprava NIS                       http://www.nspuh.cz
} : >NsP Uherske Hradiste          phone : voice +420 0632 529 204
} : >Purkynova 365                         fax   +420 0632 551 014
} : >686 68 Uherske Hradiste  Czech Republic
} : >--------------------------------------------------------------
} 
} --
} **************************************************************************
} Gil Payson
} gil@gilbaby.dinoco.de
} g.payson@edina.xnc.com
} grp@geac.com
} **************************************************************************


Tino