How to query on differences between times

[Siegfried Heintze]

I have a table with fields called start and stop of type date/time. These
represent the start and stop times various scheduled tasks.

How would I write an sql query to select all the records that are older than
2 days?
How would I write an sql query to select the record oldest start time?
How would I write an sql query to select the task with the longest run
execution time where the execution time is the difference between the stop
and the start time?

Thanks,
Siegfried

 

[KARL DEWEY]

Try these --
How would I write an sql query to select all the records that are older than
2 days?
SELECT StartStop.Data, StartStop.Start
FROM StartStop
WHERE (((StartStop.Start)<Date()-2));

How would I write an sql query to select the record oldest start time?
SELECT Min(StartStop.Start) AS MinOfStart, First(StartStop.Data) AS
FirstOfData
FROM StartStop;

How would I write an sql query to select the task with the longest run
execution time where the execution time is the difference between the stop
and the start time?
SELECT Max([Stop]-[Start]) AS Expr1, First(StartStop.Data) AS FirstOfData
FROM StartStop;

 

['69 Camaro]

Hi, Siegfried.

This sounds suspiciously like a homework assignment, but there's a good
chance that it might not be.

How would I write an sql query to select all the records that are older
than 2 days?

If "older than two days" means "more than 48 hours," then use the following:

SELECT *
FROM tblTasks
WHERE (Start < Now() - 2);

You can replace Now() with Date(), but that will allow records of up to, but
not including 72 hours old to still be "less than two days old," so I'd
recommend using Now().

How would I write an sql query to select the record oldest start time?

Try:

SELECT MIN(Start) AS OldestStart,
Task AS OldestTask
FROM tblTasks
WHERE (Start =
(SELECT MIN(Start)
FROM tblTasks))
GROUP BY Task;

You might be tempted to use FIRST(Task) to find the oldest task, but that
will return the value in the task field for the first record in the table,
which might not be the task in the same record as MIN(Start).

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.