Comparing Dates/Times with DateDiff Function

[emozley]

What is the best way of using DateDiff to compare two events, both of which have a date (dd/mm/yyyy) and a time (hh:mm:ss). Basically I am trying to make a bulletin board that displays how many messages have been posted since a user's last visit.

I have 4 variables:

DatePosted (this is the date the message was posted)
TimePosted (this is the time the message was posted)
DateVisited (this is the date of the user's last visit)
TimeVisited (this is the time of the user's last visit)

I think I need to have a nested If...Then...Else structure that looks does a DateDiff looking at the year part and if that is in the future then we can we don't have to worry checking other parts of the date. However it could be the same year but a different month so this would have to be checked. If the month was in the future then again we could bail out but it could be the same month but the day could be ahead. I would then go all the way down to the seconds part of the time posted and the timevisited.

Any ideas on how to actually put this together? I am having a nightmare with the logic!

Any help much appreciated.

Ed

 

[Sheco]

Can you just get your recordset with SQL like this:

SELECT * 
FROM TableMessages 
WHERE (DatePosted > DateVisited)
   OR ((DatePosted = DateVisited) AND (TimePosted > TimeVisited))

 

[emozley]

Unfortunately not as the DatePosted/TimePosted is stored in a table called 'Bulletins' and the DateVisited/DatePosted is in a table called 'Users'.

Unless there was some sort of join that pulled the two tables together based on the criteria you described? Would that be possible?

 

[Sheco]

I assume that you'll probably be searching the Users table anyway to verify the login... perhaps you could pull the date and time of the last visit at the same time?

... either that or use a subquery.

 

[emozley]

I've now changed the database format so the date is now 'General Date' format eg 18/07/2005 18:38:06 all in one field and with a bit of luck I can just do a basic compare (hopefully ASP will treat the all in one date/time as a floating point number or something.

 

[DotNetGnat]

Unfortunately not as the DatePosted/TimePosted is stored in a table called 'Bulletins' and the DateVisited/DatePosted is in a table called 'Users'.

this is not a problem...you can do joins...let us more so that we can help you out...
-DNG

 

[emozley]

Hi - in the end I settled to have combined date/time fields in each table. I then loaded the LastVisit into a variable and was then able to do a

rs.Open "SELECT * FROM Bulletins WHERE Posted > " & LastVisit, DB

Thanks for your help

 

[Sheco]

Thanks for the followup. It is always nice to know that the issue was actually solved.