Querying against current time in Field Calculator / Def Query

Question

Hi

Let's see how this new format works....

I'm attempting to query against current time in the Field Calculator - ideally what I'm trying to do is come up with a definition query that provides features that are greater than 3 hours old.

What I have is a table from MS Access 2010, that is being delivered to ArcGIS via a database link. All working well.

Each record in the table has an X and Y which is being drawn via an event viewer - all working ok.

Each record also has a timestamp of the time it was created. I wan to be able to separate out records that are greater than 3 hours old, so that they can be drawn in a different colour to highlight their importance.

So far I've discovered the CURRENT_TIMESTAMP SQL operator, but can't seem to get anywhere with it.

Time field in the table is formatted as dd/mm/yyyy hh:mm:ss AM/PM

Is anyone able to provide some clarity on this?

Help!

AnthonyCheesman1 · Answer

Thanks Dan, Ted. I'd forgotten about the DateDiff function until after I posted this. I couldn't get it to fire but I think that was more to do with my syntax. And after a weekend of reflection, I seem to recall using it for a project about 2 years ago, and I'm fairly sure I kept some good notes from that.

So first task this morning will be to dig back through my archives.

Thanks again and I'll report back with success or otherwise.

TedKowal · Answer

Using a Def Query you may want to try something like this ...

Select ..... where DateDiff('h','Timestampdate', now()) > 3    (Note dates always get me confuse you may have to switch the Timestampdate and the Now() function. -- this will only work against an access database.)

TedKowal · Answer

MS Access doe not have a classical "Timestamp".  Generally one would create a macro or function to generate this data on a record in access.  MS SQL does have a Timestamp.  If you are wanting to process or use a comparative script/sql try passing the sql using a DB Pass-through query.  This would allow you to use all MS Access functionality instead of your DSN/ODBC translations (your database link?).   I believe you would then use the Access "Now()" function to compare the time values.

This is all that comes to mind at moment -- hope this can lead you somewhere.