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