Posted by Erland Sommarskog on 11/09/06 22:39

(santiago@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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Re: Schema changes to SQL Server
• Prev in forum: Re: Please Help With Complex Update Statement Logic
• Thread view: Re: select records between dates - query works in VS 2005 but it doesn't in asp 3

[Reply to this message]