>> I'm trying with (to get all records from Feb 2nd) >> >> where >> (MyTimeStamp between '02-01-2012 00:00:00' and '02-01-2012 >> 23:59:59') >> >> it uses the index, but I don't get all the records > >My mistake!... that seems to work OK... Now I just wonder if that is the > *best* way to do that... So far I see no problem, but just in case...
Well, apart from it being impossible to get records from Feb 2nd through querying 2 Jan or 1 Feb (depending on the date conversion routines your tool uses - as far as I know, the three formats Firebird supports are dd.mm.yyyy, mm/dd/yyyy and yyyy-mm-dd, so neither dd-mm-yyyy nor mm-dd-yyyy are understood by Firebird), you can miss some records from the last second (let's say 02-01-2012 23:59:59.1234). Using where MyTimeStamp between '02-01-2012' and '03-01-2012' and cast(MyTimeStamp) as Date = '02-01-2012' will get you all AND use an index (BETWEEN uses the index, CAST ascertains you return the correct rows). HTH, Set
