SQL query selecting between two dates

问题:

I want to select records between two dates - a startDate and endDate (they are date/time format in sql). I have the following sql query but it does not work, could someone tell me what I'm doing wrong?

"SELECT * 
FROM house 
WHERE startDate >= '2012/02/22 00:00:00' AND endDate <= '2012-02-25 00:00:00'"

回答1:

I would suggest converting the dates to a datetime and comparing them as well as keeping the date standard and consistent. Something like:

"SELECT * 
FROM house 
WHERE DATE(startDate) >= DATE('2012-02-22 00:00:00') 
  AND DATE(endDate) <= DATE('2012-02-25 00:00:00')"

NOTE: I assumed your startDate and endDate were of the same format as the strings your provided.

回答2:

Do you want all rows that startDate is '2012-02-22' or later and endDate is '2012-02-22' or previous? Then, use this:

SELECT * 
FROM house 
WHERE startDate >= '2012-02-22' 
  AND endDate < '2012-02-26'           --- notice the `<`, not `<=`
                                       --- and the `day+1`

---

When using dates with SQL products, better use this format in queries and statements: '20120222' or this (which I find easier to read: '2012-02-22'.

Using slashes like '2012/02/22' or any other order than Year-Month-Day is not recommended.

There's no need to include the time part. '2012-02-22 00:00:00' is the same as '2012-02-22'.

Using endDate <= '2012-02-25 00:00:00' means that any row with date 25nd of Feb. 2012 but time after midnight ('00:00:00') will not match the condition. If you want those rows, too, use endDate < '2012-02-26' instead.

You could use DATE(endDate) <= DATE('2012-02-25 00:00:00') or DATE(endDate) <= '2012-02-25' but these conditions are "un-sargable", so your queries will not be able to use an index on endDate.

回答3:

There is the builtin STR_TO_DATE function in MySql that takes same format mask as date_format.

start_date >= str_to_date('2012/02/22 00:00:00','%Y/%m/%d %h:%i:%s)