How do I do a SQL range for date and time dimensio

问题:

I have a reporting table that has a saledateid (days) and saletimeid (minutes) dimensions. I need to select a range that may be less or more than one day. If the range is more than one day (e.g. (1705, 901) -> (1708, 1140) to represent 2015-09-01 15:00:00 -> 2015-09-04 18:59:00) I can use:

WHERE (saledateid = 1705 AND saletimeid >= 901)
   OR (saledateid BETWEEN 1705 + 1 AND 1708 - 1)
   OR (saledateid = 1708 AND saletimeid <= 1140))

However, this doesn't work when the saledateid's are the same (less than one day) like (1708, 901) to (1708, 1140) (only a 4 hour period) because the whole day will be returned. I would have to use:

WHERE saledateid = 1708 AND saletimeid BETWEEN 901 AND 1140

Is there a correct expression I can use to cover both scenarios or do I have decide in application code before executing the SQL?

回答1:

I think this will do what you want:

where (saledateid > 1705 or
       saledateid = 1705 and saletimeid >= 901
      ) and
      (saledateid < 1708 or
       saledateid = 1708 and saletimeid <= 1140
      )