Date/time precision in Access

[Killer42]

Hi all.

I have an interesting situation; searching a Date field for my exact value fails, but including it in a range works.

I know this sounds like the usual beginner's date/time woes but I don't believe it is. The reason for the "Date/time precision" title is that I would like to know whether anyone can tell me to what precision a date/time value is stored in Access. And how it might be affecting my search. (I have spent some time fruitlessly searching TSDN.)

I haven't been able to find any way to display a date/time down to tenths of a second - only down to the second. But is that exactly what is stored? Because here's my weird situation... (note, just one example - seems to apply to

I have a field called TheTime. Field type is Date/Time. There is a record with the value "06/13/2007 07:51:00". (Though it's painful, I'll use U.S. format for consistency with the SQL). If I execute this query...

SELECT * FROM PerMinuteStats WHERE TheTime = #06/13/2007 07:51:00#

...it returns no records.

If I execute this query...

SELECT * FROM PerMinuteStats WHERE TheTime Between #06/13/2007 07:51:00# And #06/13/2007 07:51:01#;

...it returns the record which I knew was there.

So as far as I can tell, the value must be somewhere between 07:51:00 and 07:51:01. This then raises a few questions, such as

1. How the heck do I find the exact record? Values like "07:51:00.9 " just produce a "syntax error" response.
2. How can I see the exact value that's in it?
3. How did it get there? (I'll be looking into this if and when it seems necessary).
4. Am I totally on the wrong track?

[puppydogbuddy]

Hi all.

I have an interesting situation; searching a Date field for my exact value fails, but including it in a range works.

I know this sounds like the usual beginner's date/time woes but I don't believe it is. The reason for the "Date/time precision" title is that I would like to know whether anyone can tell me to what precision a date/time value is stored in Access. And how it might be affecting my search. (I have spent some time fruitlessly searching TSDN.)

I haven't been able to find any way to display a date/time down to tenths of a second - only down to the second. But is that exactly what is stored? Because here's my weird situation... (note, just one example - seems to apply to

I have a field called TheTime. Field type is Date/Time. There is a record with the value "06/13/2007 07:51:00". (Though it's painful, I'll use U.S. format for consistency with the SQL). If I execute this query...

SELECT * FROM PerMinuteStats WHERE TheTime = #06/13/2007 07:51:00#

...it returns no records.

If I execute this query...

SELECT * FROM PerMinuteStats WHERE TheTime Between #06/13/2007 07:51:00# And #06/13/2007 07:51:01#;

...it returns the record which I knew was there.

So as far as I can tell, the value must be somewhere between 07:51:00 and 07:51:01. This then raises a few questions, such as

1. How the heck do I find the exact record? Values like "07:51:00.9 " just produce a "syntax error" response.
2. How can I see the exact value that's in it?
3. How did it get there? (I'll be looking into this if and when it seems necessary).
4. Am I totally on the wrong track?

Killer,
You need to use the Access DateValue and TimeValue functions if you are querying the date or time components individually of a field that is date/time data type.

SELECT * FROM PerMinuteStats WHERE TimeValue(TheTi me) Like "07:51:00 AM"

see this link: http://www.techonthenet.com/access/f.../timevalue.php

[Killer42]

You need to use the Access DateValue and TimeValue functions if you are querying the date or time components individually of a field that is date/time data type.

Thanks for the info.

However, I'm not (at the moment) interested in searching on either component individually. I'll probably need to do that later, but right now all I want is to simply find the record, by the (full) exact value.

[Killer42]

SELECT * FROM PerMinuteStats WHERE TimeValue(TheTi me) Like "07:51:00 AM"

Actually, now that think about it, I won't need this type of search.

I'd forgotten that I already anticipated this sort of requirement when creating the table. It has fields holding redundant copies of each date/time component (day, month, hour etc.). So I'll be able to search on any combination (Eg. find everything matching 14 minutes past the hour, any time on a Tuesday) without the extra overhead of reading and converting every value for comparison (which is what TimeValue would need to do).

[Killer42]

Ok, I'm going to give up on this one for the moment. For some reason, I just can't seem to get the ADODB Recordset Find method to work consistently. Presumably I'm doing something wrong, but can't work out what. I'll have to take some time to read up on ADO a bit more.

As it turns out, because in this case each record corresponds to an entry in an array, I can get away with using the Move method instead. Which is fine by me, as I would hope it's a little faster.

[dima69]

It seems to me rather important to understand what happaned, although the workaround exists.
Dates in Access are actually a Double. You can see the value by explicitly converting Date variable into a Double by CDbl.
My guess is that the date in your table has somehow got "unexact" double value (although the date value is still exact). So what would you see if you compare the double representation of the date litera, like CDbl(#1/1/2007 11:12:13 AM#), with the "unmatching " date value from the table, by forcing it to show numeric format, like "#.000000000000 " ?

[cactusdata]

For some reason your stored DateTime value contains a millisecond part.

You can either run an update query to clean it up, or use one of these methods:

1. The method you've already tested - Between x And y - where y is one second later that x.

2. SELECT * FROM PerMinuteStats WHERE DateDiff("s", TheTime, #06/13/2007 07:51:00 AM#) = 0

3. SELECT * FROM PerMinuteStats WHERE Format(TheTime, "\#mm\/dd\/yyyy hh\:nn\:ss AM/PM\#") = #06/13/2007 07:51:00 AM#

Method #1 will be the fastest.

By the way, contrary to what is often said, time in Access (JET) is accurate to the millisecond but there is no built in way to display a resolution lower than one second.

/gustav

[Killer42]

It seems to me rather important to understand what happaned, although the workaround exists.
Dates in Access are actually a Double. You can see the value by explicitly converting Date variable into a Double by CDbl.
My guess is that the date in your table has somehow got "unexact" double value (although the date value is still exact). So what would you see if you compare the double representation of the date litera, like CDbl(#1/1/2007 11:12:13 AM#), with the "unmatching " date value from the table, by forcing it to show numeric format, like "#.000000000000 " ?

Thanks, that's the sort of thing I was asking about originally, back in June - how to see the precise value so I could tell what was going on.

I'll have a look at it this way as soon as I have time.

I would love to know, though, how I can end up with a "slightly off" date value there, when I use SQL to store a #mm/dd/yyyy# value.

[Killer42]

For some reason your stored DateTime value contains a millisecond part.

Yeah, that's the sort of thing I thought, but didn't know how to check. Dima has shown a way to find out, so by Monday I should know for sure.

You can either run an update query to clean it up, ...

Not an option (correction, not a good option), as these records are being created constantly.

1. The method you've already tested - Between x And y - where y is one second later that x.

I'm actually finding that doesn't work consistently either. It's starting to look as though I have a more general problem with the Find, at least on an ADO recordset. I need more time to pin down the details on this.

2. SELECT * FROM PerMinuteStats WHERE DateDiff("s", TheTime, #06/13/2007 07:51:00 AM#) = 0

Uh uh! No chance!
Unless Access is a lot smarter than I believe it to be, this would require it to read all the records, to perform the DateDiff function on them, to determine whether it needs to read them.
I'm perfectly happy to be corrected, if anyone knows better. But I'm dealing with tens of millions of records here.

3. SELECT * FROM PerMinuteStats WHERE Format(TheTime, "\#mm\/dd\/yyyy hh\:nn\:ss AM/PM\#") = #06/13/2007 07:51:00 AM#

I foresee the same problem as #2. In fact this would apply to anything which requires passing the value through a function to match it. It seems as though, pretty much by definition, you have to read the entire file (or index) to determine which ones to read. Somewhat self-defeating, really. I might as well just read from start to finish and check each record myself.

Method #1 will be the fastest.

True.
In fact, as I think I (later) mentioned somewhere, I've managed to get around the need for this particular FIND, because I know the sequence of the records and which one I want, so I can just MOVE to that record.

By the way, contrary to what is often said, time in Access (JET) is accurate to the millisecond but there is no built in way to display a resolution lower than one second.

That's interesting to know. Obviously a possible source of problems, though. I wonder whether it was such a good idea. (Still, I guess it allows us to use better precision in the future without having to change the data format. Unusually forward-thinking for M$.)