Click here to go to the home page
What is Qilan?
How Does Qilan Work
See Qilan Work
Questions and Answers
Supported Databases
Qilan News Sales Support Training Contact Us

June 12, 2000 - Qilan Technical Note #5

Subject: DateTime Query Technique

Suppose your table contains a datetime field. This type of field stores both the date and time. You want to create a query that finds all records in a single day. This is one technique:

Framework fields: Day_Num; Month_Num; Year_Num
Framework abacus (MDY): (Month [Day_Num] Day [Day_Num] Year [Year_Num])

Note: [MDY] returns a date with zero time and the current time zone in the correct UNIX format. For example: 2000 06 10 00:00:00 -0500. The UNIX is format is always year, month, day, hours, minutes, seconds, time zone.

Table field: DateTime
Table abacus (DateQuery): ([DateTime]&Mac179; [Aquire [MDY]) and ([DateTime] <
(Date (Acquire [MDY] + 86400)))

All records in a single day will now be returned, that is, all records whose datetime is greater than or equal to 2000 06 10 00:00:00 -0500 and less than 2000 06 11 00:00:00 -0500. We use the number of seconds in a single day (60 * 60 * 24) 86400 to advance to the next day.

Also note how the addition of a days worth of seconds is converted back to a date.

Authored by: Stephen Caine with assist from Larry Atkin

© 2001 CommonGround Softworks Inc., All Rights Reserved
Click here to visit CommonGround Softworks