>> 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