Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2003
    Posts
    3

    Unanswered: Selecting between dates AND times

    Hiya,

    Im having problems trying to select all the records between 2 certain dates and times. I have tryed the SQL statement below but it seems to only select the record on all the dates but only for the time 13:00.... Is it something very obvious that im doing wrong???

    SELECT *
    FROM [FLOW DATA]
    WHERE ((([FLOW DATA].Date >=#1/12/2002#) AND ([FLOW DATA].Time >=#13:00#)) AND
    (([FLOW DATA].Date<=#1/15/2002#) AND ([FLOW DATA].Time <=#13:00#)));



    Cheers

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your problem is bigger than you think

    the reason you are only getting 13:00 is because that's the only value that is simultaneously greater than or equal to 13:00 and less than or equal to 13:00

    which is what you asked

    what you should do is combine the date and time columns

    did you know that a date/time column always contains both a date and a time portion?

    do yourself a favour and run this:
    PHP Code:
    select [Date]
         , 
    format([Date],"yyyy-mm-dd hh:nn:ss")
         , [
    Time]
         , 
    format([Time],"yyyy-mm-dd hh:nn:ss")
      
    from [FLOW DATA
    if you do need for some reason to keep the date and time columns separate, your query gets a lot more complicated

    you'll have to search for rows where
    - time >= 13:00 on the first day
    or
    - time <= 13:00 on the last day
    or
    - any time whatsoever on days between first and last day
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2003
    Posts
    706
    Yes, indeed. You need to use a date/time column type, storing both the date and the time in a single column-value.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  4. #4
    Join Date
    Jul 2003
    Posts
    73

    Re: Selecting between dates AND times

    Originally posted by cobs
    Hiya,

    Im having problems trying to select all the records between 2 certain dates and times. I have tryed the SQL statement below but it seems to only select the record on all the dates but only for the time 13:00.... Is it something very obvious that im doing wrong???

    SELECT *
    FROM [FLOW DATA]
    WHERE ((([FLOW DATA].Date >=#1/12/2002#) AND ([FLOW DATA].Time >=#13:00#)) AND
    (([FLOW DATA].Date<=#1/15/2002#) AND ([FLOW DATA].Time <=#13:00#)));



    Cheers
    Another way to do this (without the preferable option of storing date and time in the one variable) is as follows (apologies for poor formatting / brackets):

    Code:
    SELECT *
    FROM [FLOW DATA]
    WHERE
    (
      ([FLOW DATA].Date > #1/12/2002#
       OR ([FLOW DATA].Date = #1/12/2002#
           [FLOW DATA].Time > #13:00#))
      AND
      ([FLOW DATA].Date < #1/15/2002#
       OR ([FLOW DATA].Date = #1/15/2002#
           [FLOW DATA].Time < #13:00#))
    )
    Basically where the date is (after 1/12/2002) or (date is 1/12/2002 and time is after 13:00). And where the date is (before 1/15/2002) or (date is 1/15/2002 and time is before 13:00).

    This could be an option if you don't wish to change the database structure.

Posting Permissions

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