CODEWITHSUNDEEP
phpmysqldatetime
Benedict Lewis
Benedict Lewis

Reputation: 2813

Check if date between two others

I have the following row in a table:

id booking start               end
 3     325 2015-01-01 00:00:00 2015-01-09 00:00:00

Using the following query I attempt to find rows where a specified date is between two others:

SELECT * FROM $table WHERE booking=$product AND $from between start and end

Which is parsed to the following when run:

SELECT * FROM wp_ovalbit_utils WHERE booking=325 AND 2015-02-01 between start and end

Obviously this should return that row, however it doesn't.

This is my schema:

id mediumint(9) NOT NULL AUTO_INCREMENT,
booking text NOT NULL,
start datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
end datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
UNIQUE KEY id (id)

Upvotes: 0

Views: 100

Answers (3)

dav
dav

Reputation: 9267

SELECT * FROM wp_ovalbit_utils 
WHERE booking=325 
AND `start` < '2015-02-01 00:00:00' AND `end` > '2015-02-01 00:00:00'

so, basically your date should be more than the start date and less than the end date

Upvotes: 0

Dennis
Dennis

Reputation: 394

You should be able to use a following WHERE clause.

WHERE `start` < $from AND $from < `end`;

I can't say for sure if the columns can be used in a BETWEEN statement (at least, i've never used a BETWEEN that way around).

PS. Be aware of SQL-injection that may be possible by using variables directly. See http://wiki.hashphp.org/Validation

Upvotes: 0

juergen d
juergen d

Reputation: 204854

You forgot quotes around the date and to escape reserved words like end

SELECT * 
FROM wp_ovalbit_utils 
WHERE booking = 325 
AND '2015-02-01 00:00:00' between `start` and `end`

BTW 1st for February is not between the two example dates.

Upvotes: 2

Related Questions