Hi

I am trying to query a date range on a single datedate field in sql 2000 db - the query below will not work if I do a search on say 21/07/2003 to the 21/07/2003, although there are records on for that day.

Can anyone help ??

SELECT *
FROM dbo.WA
WHERE ((([wa].date)>='start')) AND ((([wa].date)<='finish'));

Thanks

hi donald, if you want assistance you can continue to post in the same thread and not post a new thread

You don't need all those parens to begin with, they don't hurt but it's messy. Perhaps you are confused on how these queries are run?
You should have the default database Pubs with table sales. These come standard with installation package.

run this query on it:
select *
from dbo.sales

then run this query:
select *
from dbo.sales
WHERE ord_date>='1994-09-13'
AND ord_date<='1994-09-14';

and finally this:
select *
from dbo.sales
WHERE ord_date>='1994-09-13 00:00:00.000'
AND ord_date<='1994-09-14 00:00:00.000';

notice you can leave out hours and minutes on the last 2 queries and you get the same results. Therefore when you plug in your search on the two text boxes, you can do it if it's in the same yyyy mm dd format, just make sure you do it correctly. I recommend doing drop down or even a javascript calendar for easiest error checking.

The reason your query probably isn't working: You also are using the name 'date' as one of your column names. 'Date' is a reserved keyword which is part of the grammar of the Transact-SQL language used to parse and understand Transact-SQL statements and batches. Although it is syntactically possible to use SQL Server reserved keywords as identifiers and object names in Transact-SQL scripts, this can be done only by using delimited identifiers.

http://msdn.microsoft.com/library/de...on_03_89rn.asp

easiest approach is just to rename your column a different Non reserved word name. Or you can go the delimited identifier route if you wish, it's just more annoying that way.

hope this helps

Instead of this:
WHERE ((([wa].date)>='start')) AND ((([wa].date)<='finish'));

Perhaps try this:
WHERE ((([wa].date)>=#1/1/2002#)) AND ((([wa].date)<=#1/1/2003#));

And perhaps use the month before the day.

There may be a violation according to your regional settings.

Try to place:

Session.LCID = 1033

in somewhere upwards the query.