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?
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.
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.)
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.