Informix Forum - Read-only Archive

Re: Digest 126 - 128

Posted By: Jonathan Leffler
Date: Friday, 14 April 2006, at 1:23 a.m.

On 4/12/06, informix-forum@iiug.org wrote:
> MESSAGE: (#126) datetime condition
> <http://www.iiug.org/forums/informix-forum/index.cgi?rev=126>
>
> Hi, i am trying to select a record by using the datetime. for example,
> "2/15/2006 2pm" in my database looks like '2006-2-15 14'. for 3pm i have
> '2006-2-15 15'. how could i select the whole day record without doing
> "select * from tablename where datetime = '2006-2-15 15' or datetime =
> '2006-2-15 14' or......."

So, you appear to be using a DATETIME YEAR TO HOUR column.

You can either use BETWEEN or EXTEND (which, in this case, is more
like CONTRACT).

WHERE

dtcolumn BETWEEN

DATETIME(2006-02-15 00) YEAR TO HOUR AND

DATETIME(2006-02-15 23) YEAR TO HOUR
OR

EXTEND(dtcolumn, YEAR TO DAY) = DATETIME(2006-02-15) YEAR TO DAY

Both work - the first might be marginally faster, but not by a lot (as
in, I'd be mildly surprised if you could measure the difference).

--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/

Informix Forum - Read-only Archive is maintained by Administrator with WebBBS 5.12.