select records between dates - query works in VS 2005 but it doesn't in asp 3

[santiago]

Hello. I'm having troubles with a query that (should) return all the
records between two dates. The date field is a datetime type. The db is
SQL Server 2000. When I try this

SELECT RESERVES.RES_ID, PAYMENTS_RECEIVED.PYR_ID,
PAYMENTS_RECEIVED.PYR_VALUE, PAYMENTS_RECEIVED.PYR_DATE,
CUSTOMERS.CUS_NAME
FROM RESERVES LEFT OUTER JOIN
PAYMENTS_RECEIVED ON RESERVES.RES_ID =
PAYMENTS_RECEIVED.RES_ID LEFT OUTER JOIN
CUSTOMERS ON RESERVES.CUS_ID = CUSTOMERS.CUS_ID
WHERE (PAYMENTS_RECEIVED.PYR_DATE >= '2006-03-20 00:00:00') AND
(PAYMENTS_RECEIVED.PYR_DATE < '2006-03-27 00:00:00')

on a "query builder" in visual studio, I get the results that I want.
But when I use exactly the same query on an asp 3 vbscript script, I
get no results (an empty selection).
I've done everything imaginable. I wrote the date as iso, ansi, british
format using convert(,103) (that's how users will enter the dates),
i've used cast('20060327' as datetime), etc. But I can't still get it
to work. Other querys from the asp pages work ok. Any ideas?

thanks a lot in advance

[Erland Sommarskog]

(sa******@partirviajes.com.ar) writes:

Hello. I'm having troubles with a query that (should) return all the
records between two dates. The date field is a datetime type. The db is
SQL Server 2000. When I try this

SELECT RESERVES.RES_ID, PAYMENTS_RECEIVED.PYR_ID,
PAYMENTS_RECEIVED.PYR_VALUE, PAYMENTS_RECEIVED.PYR_DATE,
CUSTOMERS.CUS_NAME
FROM RESERVES LEFT OUTER JOIN
PAYMENTS_RECEIVED ON RESERVES.RES_ID =
PAYMENTS_RECEIVED.RES_ID LEFT OUTER JOIN
CUSTOMERS ON RESERVES.CUS_ID = CUSTOMERS.CUS_ID
WHERE (PAYMENTS_RECEIVED.PYR_DATE >= '2006-03-20 00:00:00') AND
(PAYMENTS_RECEIVED.PYR_DATE < '2006-03-27 00:00:00')

on a "query builder" in visual studio, I get the results that I want.
But when I use exactly the same query on an asp 3 vbscript script, I
get no results (an empty selection).
I've done everything imaginable. I wrote the date as iso, ansi, british
format using convert(,103) (that's how users will enter the dates),
i've used cast('20060327' as datetime), etc. But I can't still get it
to work. Other querys from the asp pages work ok. Any ideas?

This is how the query in the ASP page should look like:

SELECT RESERVES.RES_ID, PAYMENTS_RECEIVED.PYR_ID,
PAYMENTS_RECEIVED.PYR_VALUE, PAYMENTS_RECEIVED.PYR_DATE,
CUSTOMERS.CUS_NAME
FROM RESERVES LEFT OUTER JOIN
PAYMENTS_RECEIVED ON RESERVES.RES_ID =
PAYMENTS_RECEIVED.RES_ID LEFT OUTER JOIN
CUSTOMERS ON RESERVES.CUS_ID = CUSTOMERS.CUS_ID
WHERE (PAYMENTS_RECEIVED.PYR_DATE >= ?) AND
(PAYMENTS_RECEIVED.PYR_DATE < ?)

and you should pass the dates in the parameters collection, using the
..Create Parameter method. The dates will then be interpreted according
to the regional settings on the machine and passed as binary values
to SQL Server.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx