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
|
