-
Access Time difference
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
-
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.
-
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 :(
-
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.
-
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
-
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! :D
-
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
-
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
-
thanx for that post..thats a great help :D
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?
-