Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2005
    Posts
    1

    Unanswered: Issue with BETWEEN MONTH() and MONTH() query

    Hi there,

    I am trying to select records which have start and end dates that span or include any given month.

    I came up with this query but for some reason it isn't giving me the results I expected:

    SELECT * FROM Orders WHERE stationid = '26' AND productid = '1' AND ((now() BETWEEN startdate AND enddate) OR ('11' BETWEEN MONTH(startdate) AND MONTH(enddate))) ORDER BY productid

    'startdate' and 'enddate' are stored in the DB as timestamp(14) fields.

    In this particular case, month = 11 (Nov) but it still retrieves orders with a startdate = 20050901000000 (Sept) and enddate = 20051005000000 (Oct).

    I have been beating myself over the head trying to figure out how to pull out records if they happen in a specific month, and I thought I finally had, until I realized this was happening.

    Any help would be appreciated. Thanks.

  2. #2
    Join Date
    Apr 2005
    Location
    Lier, Belgium
    Posts
    122
    Quote Originally Posted by impactorange
    SELECT * FROM Orders WHERE stationid = '26' AND productid = '1' AND ((now() BETWEEN startdate AND enddate) OR ('11' BETWEEN MONTH(startdate) AND MONTH(enddate))) ORDER BY productid

    In this particular case, month = 11 (Nov) but it still retrieves orders with a startdate = 20050901000000 (Sept) and enddate = 20051005000000 (Oct).
    If NOW() returns the timestamp of now (around 2005-09-28), your
    'now() BETWEEN startdate AND enddate' subclause, which is ORed with your month selection logic, is true.

    --
    felix

Posting Permissions

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