Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2003
    Posts
    18

    Unanswered: Searching for records within two dates

    I have a query that works correctly, pulling all the records for december:
    "SELECT * FROM TBLORDERSPROCESSINGHISTORY WHERE ((COLDATE > CONVERT(DATETIME, '2003-08-31', 102)) AND (COLDATE < CONVERT(DATETIME, '2003-10-01', 102))) AND (COLORGCODE = 2) AND (COLACTIONSTATUSCODE = 16 OR COLACTIONSTATUSCODE = 14) OR ((COLDATE > CONVERT(DATETIME, '2003-08-31', 102)) AND (COLDATE < CONVERT(DATETIME, '2003-10-01', 102))) AND (COLORGCODE = 1) AND (COLACTIONSTATUSCODE = 7) ORDER BY COLDATE"

    I am trying to create a search that uses input from a form, which uses a slightly different date format, so I end up with the following query (the begin and end dates are valuables that were inserted on my ASP page):
    "SELECT * FROM TBLORDERSPROCESSINGHISTORY WHERE ((COLDATE >= CONVERT(DATETIME, '9/1/2003', 101)) AND (COLDATE <= CONVERT(DATETIME, '9/30/2003', 101))) AND (COLORGCODE = 2) AND (COLACTIONSTATUSCODE = 16 OR COLACTIONSTATUSCODE = 14) OR ((COLDATE >= CONVERT(DATETIME, '9/1/2003', 101)) AND (COLDATE <= CONVERT(DATETIME, '9/30/2003', 101))) AND (COLORGCODE = 1) AND (COLACTIONSTATUSCODE = 7) ORDER BY COLDATE"

    This second query will return records from 9/1/2003, but not from 9/30/2003. Does the <= comparison not work well with dates, or am I missing something?
    -Doug Picanzi

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well you've got a bunch of other predicates...you sure that not it? Also what's the datatype of the column...why are you converting the value?

    How about some ddl of the table and sample data to work with...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Sep 2003
    Posts
    522
    Code:
    SELECT * FROM TBLORDERSPROCESSINGHISTORY 
    WHERE COLDATE between '9/1/2003' AND '9/30/2003' 
    AND (
       ((COLORGCODE = 2) AND (COLACTIONSTATUSCODE = 16 OR COLACTIONSTATUSCODE = 14)) 
       OR (COLORGCODE = 1 AND COLACTIONSTATUSCODE = 7)
    )
    ORDER BY COLDATE

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "9/30/2003" means 12:00 a.m. on 9/30/2003, and of course all events occuring on that day occur AFTER 12:00 a.m.

    You can use either of these options instead

    ... COLDATE <= '9/30/2003 23:59:59'
    ... COLDATE < '10/1/2003'
    COLDATE BETWEEN '9/1/2003' AND '9/30/2003 23:59:59'

    You don't need to CONVERT the datevalue first, unless your COLDATE field is not a datetime field, in which case you have bigger problems...
    Eliminating the superfluous parenthesis and rewriting your query like this:

    SELECT *
    FROM TBLORDERSPROCESSINGHISTORY
    WHERE COLDATE BETWEEN '9/1/2003' AND '9/30/2003 23:59:59'
    AND COLORGCODE = 2
    AND (COLACTIONSTATUSCODE = 16 OR COLACTIONSTATUSCODE = 14)
    OR
    COLDATE BETWEEN '9/1/2003' AND '9/30/2003 23:59:59'
    AND COLORGCODE = 1
    AND COLACTIONSTATUSCODE = 7
    ORDER BY COLDATE

    ...should also help you realize that you are missing parenthesis required to separate the two sides of your outermost OR clause:

    SELECT *
    FROM TBLORDERSPROCESSINGHISTORY
    WHERE (COLDATE BETWEEN '9/1/2003' AND '9/30/2003 23:59:59'
    AND COLORGCODE = 2
    AND (COLACTIONSTATUSCODE = 16 OR COLACTIONSTATUSCODE = 14))
    OR
    (COLDATE BETWEEN '9/1/2003' AND '9/30/2003 23:59:59'
    AND COLORGCODE = 1
    AND COLACTIONSTATUSCODE = 7)
    ORDER BY COLDATE

    ...but this is a much more succint syntax (smaller is better!):

    SELECT *
    FROM TBLORDERSPROCESSINGHISTORY
    WHERE COLDATE BETWEEN '9/1/2003' AND '9/30/2003 23:59:59'
    AND ((COLORGCODE = 2 AND (COLACTIONSTATUSCODE = 16 OR COLACTIONSTATUSCODE = 14))
    OR (COLORGCODE = 1 AND COLACTIONSTATUSCODE = 7))
    ORDER BY COLDATE

    Got it?

    blindman

  5. #5
    Join Date
    Sep 2003
    Posts
    522
    in other words, what i posted earlier, except changing 9/30/2003 to 10/1/2003, right?

    Code:
    SELECT * FROM TBLORDERSPROCESSINGHISTORY 
    WHERE COLDATE between '9/1/2003' AND '10/1/2003' 
    AND (
       ((COLORGCODE = 2) AND (COLACTIONSTATUSCODE = 16 OR COLACTIONSTATUSCODE = 14)) 
       OR (COLORGCODE = 1 AND COLACTIONSTATUSCODE = 7)
    )
    ORDER BY COLDATE

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes, except that "10/1/2003" would include records set to "10/1/2003 00:00:00" and often these would include records for October 1st for which the specific time is not known, and these should not be included.

    If you are going to use the BETWEEN operator, you have to use "9/30/2003 23:59:59", or to be fully accurate "9/30/2003 23:59:59.9999".

    To use "10/1/2003" you would have to use two comparisons:

    WHERE (COLDATE >= '9/1/2003' and COLDATE < '10/1/2003')

    The above statement does not include events set to "10/1/2003 00:00:00"

    blindman

  7. #7
    Join Date
    Sep 2003
    Posts
    522
    ok, grand poobah, so we just add 23:59:59.9999 to my original query and we're done with this topic

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'm done. You're done. But we still haven't heard from dyingjoy...

    "Grand Poobah" is certainly not a moniker I would bestow on myself!

    Gotta go now. It's 10/24/2003 00:11:32.4565 and my bedtime was 00:01:41.4565 hours ago.

    blindman

  9. #9
    Join Date
    Aug 2003
    Posts
    18

    Perfect

    Thanks to both of you. Your solution (and explanation) fixed things perfectly. I appreciate the help.
    -Doug Picanzi

Posting Permissions

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