Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Posts
    106

    Unanswered: BETWEEN date operator

    Does the BETWEEN operator capture the end points. For exmaple if I want to captue all records within date range of 1/1/2005 and 1/31/2005, including the first and last day, will the BETWEEN operator capture all those records or just the ones from 1/2/2005 to 1/30/2005? Thanks.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Yes, BETWEEN captures both end points.

    However, beware with dates that there is also a time component that defaults to 00:00:00. So with your example, a date of 01/31/2005 with a time component of (e.g.) 14:30:00 will fall outside the date range (which ends at 01/31/2005 00:00:00).

  3. #3
    Join Date
    Feb 2004
    Posts
    106
    Thank you. I'm using the 00:00:00 for the times so I think I will be ok.

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    just in case:

    you want to use 00:00:00 for the beginning time and 23:59:59 for the ending time in order to be inclusive.

    -Chuck

  5. #5
    Join Date
    Feb 2004
    Posts
    106
    good point.

    Another question:

    What is the Oracle equivalent of the "datediff" function in SQL Server?

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Simply subtract. For example.

    select to_date('11/07/2005 12:33','mm/dd/yyyy hh24:mi') -
    to_date('01/01/2005 23:00','mm/dd/yyyy hh24:mi') datediff
    from dual;


    DATEDIFF
    ---------
    309.56458

    Which means that the range between the two dates is 309.56458 days
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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