Different results in SQL and Access

[Pat Backowski]

Hi Everyone,

When I run the query
Select a,b,c from mytable where trandate >= #20/06/2008 00:00:01# and
trandate <= #20/06/2008 23:59:59# and (trantype = "OP" or trantype = "NP);

in SQL Server, I get the result of 145,309 rows selected
in MS-Access, with a table Linked to the SQL Server table, I get the result
of 143,516.


Does anyone have any idea why this might happen?

Many thanks in advance for your kind consideration
Pat Backowski

 

[Bob Barrows [MVP]]

Hi Everyone,

When I run the query
Select a,b,c from mytable where trandate >= #20/06/2008 00:00:01# and
trandate <= #20/06/2008 23:59:59# and (trantype = "OP" or trantype =
"NP);

in SQL Server, I get the result of 145,309 rows selected
in MS-Access, with a table Linked to the SQL Server table, I get the
result of 143,516.


Does anyone have any idea why this might happen?

Many thanks in advance for your kind consideration
Pat Backowski

I assume you have verified that the same data exists in both places ...
If so, here is my theory:

Dates are stored differently in each system, resulting in different
resolution. Two things:
1. you need to use a less ambiguous date format especially if you plan on
deploying this to systems where the regional settings might be different,
and
2. more importantly, you need to make this more "bullet-proof" in order to
use it in both. I think this will work:

where trandate >= #2008-06-20# and
trandate < #2008-06-21#

 

[BruceM]

How about:
trandate = #02/06/2008#
unless for some reason it is necessary to omit the first second of the day.
This suggests a reason why you are getting different results. The Date
function stores the date as 12:00:00 AM. It coud be that such a date is
being handled differently in the two systems (although I don't quite follow
how a table is linked to a table, but maybe that's because of my
unfamiliarity with SQL Server).

 

[Bob Barrows [MVP]]

How about:
trandate = #02/06/2008#

It sounds as if the field contains time as well as date. This criteria
will fail for a row with a trandate of 2008-06-20 11:30 AM, for example.
Of course, if the field contains no time component, then you are
correct.

unless for some reason it is necessary to omit the first second of
the day. This suggests a reason why you are getting different
results. The Date function stores the date as 12:00:00 AM. It coud
be that such a date is being handled differently in the two systems

Jet stores date/times as doubles, with the whole number representing the
number of days since the seed date and the decimal representing the time
of day.
SQL Server stores datetimes as paired integers, with the first integer
representing the number of days since the seed date (which is different
from the Jet seed date) and the second representing the number of
milliseconds since midnight - a consequence of this being that the
minimum resolution of time data is about .003 sec.

(although I don't quite follow how a table is linked to a table, but
maybe that's because of my unfamiliarity with SQL Server).

He's talking about a linked table - his description was slightly "off".
Right-click in the Tables tab of the database window and select "Link
table". Change the file type to ODBC..., select a DSN pointing at a SQL
Server database, select a table, click OK, et voila, you have a table
linked to a table in SQL Server.

 

[BruceM]

I misread 20 as 02 in the date, but even so I don't see how 11:30 AM would
fail when the specified time range is:
trandate >= #20/06/2008 00:00:01# and trandate <= #20/06/2008 23:59:59#
11:30 AM is between the first second of the day and the last second of the
day.
I wonder whether the date format (dd/mm/yyyy) is part of the problem. The
format mm/dd/yyyy is more typical as a regional setting. I still wonder
whether eliminating the first second of the day accounts for the difference.
Thanks for the explanation about linking a table to a table. I have to
admit I don't quite follow how that works, but I appreciate your taking the
time to write it down.

 

[Bob Barrows [MVP]]

I misread 20 as 02 in the date, but even so I don't see how 11:30 AM
would fail when the specified time range is:
trandate >= #20/06/2008 00:00:01# and trandate <= #20/06/2008
23:59:59# 11:30 AM is between the first second of the day and the
last second of the day.

Neither do I - it was just an obvious example that would fail your
suggested criterion.

For the OP's issue, I was thinking more of datetime values that would
fall between
#20/06/2008 00:00:00# and #20/06/2008 00:00:01#

In Access, rounding might cause those values to be accepted, but SQL
Server might reject them - that's my conjecture anyways.

 

[BruceM]

Remember, I said that I had misread the number as 02 instead of 20. Of
course 11:30 AM on June 20 will fail if the criterium for the date field is
June 02. However, if the criterium for the date field is June 20, 2008, all
date/time fields with the date June 20, 2008 will be returned. If the
criterium for the field is everything except for the first second of the
day, all values added by means of the Date function, or without a specific
time of day, will be stored as midnight on that day, and therefore will not
show up if the criterium excludes that first second of the day. That was
the point I was trying to make.

 

[Michel Walsh]

MS SQL Server does not store the date_time value in the same way, so it is
technically possible you get, in MS SQL Server:

#20/06/2008 23:59:59.501#, ie five hundred and one millisecond past
23:59:59. Jet will probably see it as #21/06/2008# and thus, be rejected.


As already suggested, try:


trandate >= #20/06/2008# and trandate < #21/06/2008#


assuming you don't really want to exclude the first second, neither the last
one, of the given day, that is.




Vanderghast, Access MVP

 

[Bob Barrows [MVP]]

Remember, I said that I had misread the number as 02 instead of 20. Of
course 11:30 AM on June 20 will fail if the criterium for the date
field is June 02. However, if the criterium for the date field is
June 20, 2008, all date/time fields with the date June 20, 2008 will
be returned.

Really? Then they've changed the way date/time criteria work since the days
I was heavily using Access.

I've got A2003 ... I'm going to go try this out now ...
Nope, it does not work the way you suggest

This query:
SELECT Table1.ID, Table1.VendorCtry, Table1.VendDate
FROM Table1
WHERE (((Table1.VendDate)=#6/20/2008#));

returns a single row, the one containing #6/20/2008#, and filters out the
row where I entered #6/20/2008 11:30#
It still seems to be working the way I remember it working in the A97 days:
if times are entered, the best way to get all records for a date, regardless
of the time, is to use a date range to include all the times within that
date. Of course, it's possible to use the performance-killing technique of
using functions to strip the times from the date/time values in the field
before comparing to the date criterion value ...

I have not installed A2007, so if you are talking about a new feature in
that version, then you have the advantage of me.

 

[BruceM]

I'm not using Access 2007, and it was my mistake to look too quickly at a
query where I had already filtered records as I had described, but I had
used DateSerial (didn't notice that it killed performance, though). I could
have used DateValue, I suppose. There are several other possibilites I can
think of.
Anyhow, I did some experimenting, and found that this criterium works:

= #06/20/08# AND < #06/20/08#

Records for which a time was not entered, or that were entered by means of
the Date function, will show up. If the intention in starting from 00:00:01
was to eliminate such records (i.e. records that show midnight as the time),
I agree that the full date/time criterium is needed.
I still think the reason for the difference in the recordsets was due to
starting at 00:00:01 rather than 00:00:00.
I've learned some new stuff here, so this has been a productive exchange for
me, even if the OP has departed for other pastures.

 

[Bob Barrows [MVP]]

I'm not using Access 2007, and it was my mistake to look too quickly
at a query where I had already filtered records as I had described,
but I had used DateSerial (didn't notice that it killed performance,
though).

You wouldn't have unless
1. It was a large table and
2. you had an index on that date field that could have been used to
optimize the query

Applying a formula to the field in the WHERE clause (such as DateSerial
or DateValue) prevents the use of that index which forces a table scan.
The idea is to create a "sargable" criterion, by constructing it in such
a way that the field value is left undisturbed in order to test the
criterion.

So this is sargable:
datefield > Date() - 60

This is not sargable and will prevent the use of an index on datefield:
datefield + 60 < Date()

See the difference? The query engine has to cycle through every record,
adding 60 to the value in datefield before comparing it to Date().
Whereas with the sargable criterion, the query engine merely has to
calculate Date()-60, and then use the result to filter the results from
an index.

I still think the reason for the difference in the recordsets was due
to starting at 00:00:01 rather than 00:00:00.

Yes, that is the crux of my theory as well - see Michel Walsh's reply
for a better explanation of what I was trying to get at to explain the
different results. What I did not mention was that the use of :01 might
have caused the results to be wrong for both systems, in addition to
being different.

 

[BruceM]

Thanks for the information (and for introducing me to the term "sargable").
One more thing to keep in mind as my projects expand in size. I see the
difference between applying a function or calculation to an indexed field
value and manipulating the criteria instead to get the same result. I use
indexes according to my understanding of when and why to do so, but I can't
say I have that firm a grasp on the nuances of indexing.
I read Michel Walsh's reply, and can see where the use of 01 could lead to
differences between the two results, as well as inaccurate results in both
situations.

 

[Bob Barrows [MVP]]

My,oh,my

Quite a discussion regarding the storage and selection of date/time
access vs sql server.

Thanks to you all. - Yes I am using a linked table and Yes there is
time involved.

After reading all your kind suggestions, I think I'll never use
Access to select records from SQL via Date/Time again.

Why? My first suggestion should work ...

 

[Pat Backowski]

Hi Bob,

I appreciate your suggestion, but I need to reliably select data by the hour
as well as the day.

In a commercial sense, we've fallen flat on our face and I can't take the
risk of another failure.

So, for various reasons, the sql/vb approach has merits.

Regards,
Pat

 

[Bob Barrows [MVP]]

By "sql/vb", do you mean using a recordset to loop through and select your
data?? <gasp>

Or do you mean using ADO to execute your sql queries (which would certainly
be acceptable)?

I still don't understand why you are blaming Access for the failure of your
incorrectly-constructed query*. The query I suggested you use should be the
same query you should use in your ADO code.


* and before you say "I'm not blaming Access", the statement "I'll never use
Access again" certainly carries the implication of blame.

 

[Pat Backowski]

Bob,

When quoting, please use the quote in its entirety:

"After reading all your kind suggestions, I think I'll never use
Access to select records from SQL via Date/Time again. "

I'll certainly continue to use MS-Access for lots of good reasons

Kind Regards,
Pat Backowski



If you are

 

[Bob Barrows [MVP]]

Bob,

When quoting, please use the quote in its entirety:

"After reading all your kind suggestions, I think I'll never use
Access to select records from SQL via Date/Time again. "

What difference does the entire quote make? It still implies the same thing
to me: Access is to blame for your failure to select records via date/time,
rather than your incorrectly constructed query.

I'm not trying to be argumentative here. The point I am trying to make is:
poorly constructed queries will return incorrect results regardless of the
method used to execute them.