Different results in SQL and Access

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

Pat Backowski wrote:
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#

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

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]" wrote in message
...
Pat Backowski wrote:
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#

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

BruceM wrote:
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.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

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]" wrote in message
...
BruceM wrote:
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.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

BruceM wrote:
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.


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

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.

"Bob Barrows [MVP]" wrote in message
...
BruceM wrote:
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.


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

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



"Pat Backowski" wrote in message
...
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

BruceM wrote:
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. :-)

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

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]" wrote in message
...
BruceM wrote:
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. :-)

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"