Re: Datetime column index access

Octavio G'mez wrote: > Hi everybody, > > I have a table with a datetime column (year to second) and I created a index > on that column. > > When I try the next sentence: > > Select * from table1 where extend(date_a, year to day) = today (or > any date, datetime(year to day) value)> > The access is sequential. > > How can I do to access that column in indexed path? > > Of course, if I alter the column (year to day) the index will be used, but > in this case table structure cannot be modify Sounds like bad database design, but if you can't change it... Your index is on date_a, so you can't modify that if you want to use the index. You will have to modify the date value. Try something like: SELECT * FROM table1 WHERE date_a BETWEEN EXTEND(TODAY, YEAR TO SECOND) AND EXTEND(TODAY+1, YEAR TO SECOND)-1 UNITS SECOND Or something... Otherwise you could write a function that returns DATE(date_a) and create a functional index on that. Then you could use something like: SELECT * FROM table1 WHERE my_date_function(date_a) = TODAY Cheers, -- Mark. +----------------------------------------------------------+-----------+ | Mark D. Stock mailto:mdstock@MydasSolutions.com |//////// /| | Mydas Solutions Ltd http://MydasSolutions.com |///// / //| | +-----------------------------------+//// / ///| | |We value your comments, which have |/// / ////| | |been recorded and automatically |// / /////| | |emailed back to us for our records.|/ ////////| +----------------------+-----------------------------------+-----------+ sending to informix-list