Query vs. time

Hi folks, Have a problem over which I've been slaving for two days. I obviuosly need assistance! First: Informix 7.11-UC1 on Solaris 2.5 I need to query the database and retrieve all records matching the search criteria over a period of time. For example, I need to obtain all records written within the past 24 hours matching my criteria. My initial thought was to use today - interval. However, I will need to be more specific concerning the time. I have a need to obtain records entered between 23:59:30 today and 23:59:30 yesterday. Additionally, I may need records between varying timeframes, such as between yesterday at 4:00:00 PM and 16 hours prior to that time. This job will frequently run from cron as follows: -------------------- #!/bin/ksh RUNDATE=`date +%D 23:59:30` INTERV=1 SELECT assorted_fields FROM some_table WHERE some_condition AND datetime_field >= DATETIME("${RUNDATE}") - INTERVAL ($INTERV) DAY TOSECOND AND datetime_field < DATETIME("${RUNDATE}"); --------------------- datetime_field is specified as year to second. RUNDATE and INTERV will be passed as arguments to the script and are shown here hardcoded for clarity and debug purposes. An additional thought was to use the BETWEEN function. datetime_field BETWEEN ("${RUNDATE1}") and ("${RUNDATE2}") Here the problem is obtaining the prior date and time field in UNIX given the fields variability. I am truely lost and would appreciate any help you can provide. Thanks in advance, Jeff McClure Specialist DB Analyst Ameritech New Media Jeff.W.McClure@Ameritech.com -----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading