Access Time difference

[chetz]

I want to create a query that selects a date and time entry in a database compares them and if the difference is greater than 8 hours display the details.

is this possible?? i've managed this with days using DATEDIFF but i have no idea how to do this with time i tried:

SELECT * FROM tblProblem WHERE DATEDIFF(""h"",[LastUpdateTime],TIME())=8 And Status<8 And Priority=2"

This is as an sql string in VB6 it doesnt give any kind of error message but doesn work either, i also tried changing the 8 to "08:00:00" but that had the same effect.

This query needs to work on an Access 2000 database.

Any help would be MUCH appreciated

Thanx

chetz

[BruceG]

One thing I see is that you are doing an "equal" comparison, so you will only get results if the time difference is EXACTLY 8 hours (no minutes, no seconds). I would first try a >= comparison.

[chetz]

tried >=8 but that did the same thing...returns nothing

any other suggestions??

i've been working on this for the past few days with no success

[BruceG]

Try using NOW() instead of TIME(). It looks like the TIME function has a date component which uses the "origin" date of 12/30/1899 - thus, the DATEDIFF function would always return a negative value to your query, resulting in no hits.

[localhell]

Time() returns only time and has no date component
DateDiff returns integer representing number of intervals betweem two dates (both 17.30 V 9.30 and 17.00 V 9.30 will return the same result)

I tried the fullowing :
Dim rs As New ADODB.Recordset
Set rs.DataSource = DataEnvironment1
If DataEnvironment1.Connection1.State = adStateClosed Then DataEnvironment1.Connection1.Open
DataEnvironment1.Connection1.Execute "Select DateDiff('h', '08:00', Time())"
rs.Open "Select DateDiff('h', '08:00', Time())", DataEnvironment1.Connection1

and it gave me correct result 8 hours (in my timezone
I used Access2000 of course
If can't solve send a database file with only one table and I'll try out

[chetz]

Thats worked...so the resulting recordset will be details of logs that have a diference of 8 hours or more??

i'm pretty sure thats working...

THANX A LOT for that...its easy when you know how isnt it!

[chetz]

is it possible to get the difference in minutes using a similar query?? for example 15 minutes instead of 8 hours? if so, how would this be done?

Regards

Chetz

[localhell]

You can use 'n' to get a difference in minutes, here a full list of intervals You can use :
yyyy Year
q Quarter
m Month
y Day of year
d Day
w Weekday
ww Week
h Hour
n Minute
s Second

[chetz]

thanx for that post..thats a great help

just another quick question...i need this query to work over a 24hour period and maybe even over a few days..the application it is used in runs 24/7, would this still work?

[localhell]

Yes