community.borland.com

Article #25539: Querying for the time portion of a Datetime field

Problem:
How do you phrase an SQL query for a certain time of day in an IB
DateTime field?  I would like to be able to query for every record where the
time is, say 06:30, irregardless of the date.  Is there a way to do that?






Solution:

There are a couple of approaches to this problem that will work:

1. Use the UDF, f_stripDate in FreeUDFLib.

If your server is Windows95 / NT you can obtain freeudflib from InterBase's
web site and use it.  There is a function called F_StripDate which will
take an InterBase date and strip the date part leaving the time.  So you can write
something like:

SELECT * FROM mytable
WHERE F_stripDate(theDate)='06:10:00';

2.  You can create a separate field for time. 

Querying for the time is like asking for a substring.  
If you can use UDF's that's probably somewhat better than
the alternative, casting the field to a varchar (25) and searching for
the time as a substring.  

3. You can cast the field to varchar(25) and search for the
     time as a substring.

Note: 
None of those operations can be accelerated with an index.  If you
regularly use time independent of date, you could store it as a separate
column with its own index.  Triggers that use the UDF function will
make maintaining such a column relatively painless.  In a large table,
the overhead of storing and indexing a second column will be minor
compared to the time required for a full search of the whole table.

source: list serve contributors, primarily Ann Harrison

Last Modified: 26-OCT-00