SELECT * FROM tmp_dtm_test
WHERE dtm_test BETWEEN '2007-09-20 00:00:00' AND '2007-09-20 23:59:59';

SELECT * FROM tmp_dtm_test
WHERE DATE(dtm_test) = TODAY;

CREATE TABLE "informix".tmp_dtm_test(
   dtm_test DATETIME YEAR to SECOND NOT NULL
);

SET ISOLATION TO DIRTY READ;

INSERT INTO tmp_dtm_test(dtm_test)
  SELECT DBINFO('utc_to_datetime', connected) FROM sysmaster:syssessions;

INSERT INTO tmp_dtm_test(dtm_test)
  SELECT (dtm_test) - ( 1  UNITS DAY) FROM tmp_dtm_test;

INSERT INTO tmp_dtm_test(dtm_test)
  SELECT (dtm_test) - ( 2  UNITS DAY) FROM tmp_dtm_test;

INSERT INTO tmp_dtm_test(dtm_test)
  SELECT (dtm_test) - ( 3  UNITS DAY) FROM tmp_dtm_test;

INSERT INTO tmp_dtm_test(dtm_test)
  SELECT (dtm_test) - ( 4  UNITS DAY) FROM tmp_dtm_test;

CREATE INDEX "informix".i_tmp_dtm_test ON "informix".tmp_dtm_test(dtm_test);

UPDATE STATISTICS LOW FOR TABLE tmp_dtm_test;

QUERY:
------
select {+ EXPLAIN} * FROM tmp_dtm_test
WHERE DATE(dtm_test) = TODAY;

DIRECTIVES FOLLOWED:
EXPLAIN
DIRECTIVES NOT FOLLOWED:

Estimated Cost:  3 
Estimated # of Rows Returned:  8 

   1 ) informix.tmp_dtm_test: SEQUENTIAL SCAN

        Filters: DATE (informix.tmp_dtm_test.dtm_test ) = TODAY


QUERY:
------
select {+ EXPLAIN} * FROM tmp_dtm_test
WHERE dtm_test BETWEEN '2007-09-20 00:00:00' AND '2007-09-20 23:59:59';

DIRECTIVES FOLLOWED:
EXPLAIN
DIRECTIVES NOT FOLLOWED:

Estimated Cost:  1 
Estimated # of Rows Returned:  9 

   1 ) informix.tmp_dtm_test: INDEX PATH

    ( 1 ) Index Keys: dtm_test   (Key-Only)  (Serial, fragments: ALL)
        Lower Index Filter: informix.tmp_dtm_test.dtm_test >= datetime( 2007 - 09 - 20   00 : 00 : 00 ) year to second

        Upper Index Filter: informix.tmp_dtm_test.dtm_test <= datetime( 2007 - 09 - 20   23 : 59 : 59 ) year to second