Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2002
    Nottingham - UK

    Unhappy Unanswered: Date Range Search Problem


    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'));


  2. #2
    Join Date
    Jul 2003
    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.

    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

  3. #3
    Join Date
    Jun 2003
    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.
    J. Paul Schmidt, Freelance Web and Database Developer
    Access Database Sample, Web Database Sample, ASP Design Tips

  4. #4
    Join Date
    Aug 2003
    There may be a violation according to your regional settings.

    Try to place:

    Session.LCID = 1033

    in somewhere upwards the query.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts