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 - How the heck do I find the exact record? Values like "07:51:00.9 " just produce a "syntax error" response.
- How can I see the exact value that's in it?
- How did it get there? (I'll be looking into this if and when it seems necessary).
- Am I totally on the wrong track?
8 6130
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- How the heck do I find the exact record? Values like "07:51:00.9 " just produce a "syntax error" response.
- How can I see the exact value that's in it?
- How did it get there? (I'll be looking into this if and when it seems necessary).
- 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
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.
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).
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 181
Recognized Expert New Member
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 " ?
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
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.
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$.)
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Donnal Walter |
last post by:
I was very surprised to discover that
>>> import datetime
>>> x = datetime.date(2004, 9, 14)
>>> y = datetime.datetime(2004, 9, 14, 6, 43, 15)
>>> print x == y
True
How can these two objects be considered equal? Is there a *general* way
to test for date != datetime as well as 4.5 != 4.6?
|
by: MyndPhlyp |
last post by:
I am about to completely lose what is left of my poor mynd! The historical
dissertation gets rather verbose, so I hope you can wade through it all. I
really need some help on this one or I'm looking at rewriting all my hard
work in something else (wasting probably a month or more).
(If you want to run and hide now, the error message is "The decimal field's
precision is too small to accept the numeric you attempted to add." I
haven't yet...
|
by: Riley DeWiley |
last post by:
I have an UPDATE query that is always setting 0 records. When I cut and
paste the SQL into Access and use it, it fails in the same way unless I
coerce the date fields to be '=now()', in which case it works. I never get
errors, and if I don't try to update the date fields, I always update the
expected number of rows (1). When I do date conversion using the same
conversion function, then call CRowset::Add() to add a record to the same
table,...
|
by: Robert |
last post by:
I would like to run a report for each month over two years. I am currently
using a date range like this. Then manually substitute the error_time
bounds for each month and rerun the query. How can I script this so I can
programmatically perform the substitution in a loop. Thanx in advance.
select count(*) from application_errors
where error_message like 'Time%'
and error_time >= '1Apr2004' and error_time < '1May2004'
|
by: Luis P. Mendes |
last post by:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hi,
I've inserted a couple hundred rows in a table in Postgres via psycopg2.
The first field of each row is a certain unix time (since epoch) when an
event occured.
When I try to access that database with psycopg2, I get rounded values
| |
by: Rajat |
last post by:
Hi,
I have to draw a real time chart in which I have several entries at the same
second (i.e. HH:MM:SS:Milleconds)
The charting component only takes julian date for drawing the dates.
I am not able to convert the normal C# DateTime to juliandate which takes
milliseconds along with hour,minute,second. All of the julian date
implementations only take date + HH:MM:SS (upto second not milliseconds).
Please help, how can I include the...
|
by: Donkey |
last post by:
Hi,
The precision of built-in date type of C is very low. Even using long
double float type or double float type, we can only use 12 or 16 digits
after the decimal point. What can we do if we want to use
high-precision number such as the number with 50 digits after the
decimal point. Can we define a user date type?
|
by: rushaustin |
last post by:
Hello,
In SQL, if I do
Select cast (37797.8159722222 as datetime)as DateFromDecimal
i get 2003-06-27 19:34:59.997 as the return.
in VB.NET if I do:
Dim idate As Date
idate = Date.FromOADate(37797.8159722222)
I get 6/25/2003 7:35:00 PM as the return.
|
by: Gugale at Lincoln |
last post by:
Hi,
I am working on an application which uses date as a primary key. All my
records are at least a few millisecond apart and are in the form
"20070630T12:50:24.207". SQL Server has a precision of 1/3000 of second to
store dates. I would like to bring down the precision of my data to match
SQL Server's precision. I am doing this to avoid conflicts in ADO.Net
dataset. ADO.Net is more precise and accepts duplicate rows when it
shouldn't...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |