RE: [firebird-support] Re: selecting between dates with timestamps fields

[Svein Erling Tysvær]

 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., 
mm/dd/ and -mm-dd, so neither dd-mm- nor mm-dd- 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

[firebird-support] Re: selecting between dates with timestamps fields

[tomsee7]

--- In firebird-support@yahoogroups.com, Sergio H. Gonzalez 
shg_sistemas@... wrote:

 Hello! I have a timestamp field with an index
 
 how can I select the fields from a given date, but using the index?
 
 If I do:
 
 where
 ((cast (MyTimeStamp as date)) between :from_date and :to_date)
 
 I guess that is better not to do the cast and pass the params with date 
 + time.
 
 I use Delphi and I don't know how to add the time to the date in order 
 to pass the parameter to the query...
 
 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
 
 Thanks
 
 -s
 

Hi Sergio,

I always use dd-mmm- to avoid any ambiguity:

Therefore: 
between '01-Feb-2012 00:00:00' and '01-Feb-2012 23:59:59'

Tom

[firebird-support] Re: selecting between dates with timestamps fields

[Sergio]

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

-s