MySQL datetime comparison

Question

this is my problem:

I have a table in my mysql database which contains some products, and it has two columns called "date_from" and "date_to" which determine the period when the product is active.

On my website a have a form where the admin can choose two datetime values and if the product's active period intersects the span that the admin chose, the page updates a product list.

This is my query, which I am using to achieve what I want:

$sql="SELECT * FROM presents WHERE (date_from>".$date_from." AND date_to<".$date_to.") OR (date_from>".$date_from." AND date_to>".$date_to.") OR (date_from<".$date_from." AND date_to>".$date_to.") OR (date_from<".$date_from." AND date_to<".$date_to.")";

I get this error though:

mysql said: MySql error number: 1064 MySql error decription: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.26. 12:11:43 AND date_to<2012.07.30. 12:11:46) OR (date_from>2012.07.26. 12:11:' at line 1

Any ideas?

*EDIT

I changed the query as suggested and added single quotes to variables. Now I don't get the error, but the query doesn't seem to do what it's supposed to - the product list stayes the same... Here's the query now:

$sql="SELECT * FROM presents WHERE (date_from>'".$date_from."' AND date_to<'".$date_to."') OR (date_from>'".$date_from."' AND date_to>'".$date_to."') OR (date_from<'".$date_from."' AND date_to>'".$date_to."') OR (date_from<'".$date_from."' AND date_to<'".$date_to."')";

Let's say I have this row in my table:

ID Name date_from date_to

1 test 2012-07-24 16:40:00 2012-07-31 16:40:00

---

I need all of these periods to return the row:

2012-07-23 16:40:00 to 2012-08-01 16:40:00

2012-07-23 16:40:00 to 2012-07-28 16:40:00

2012-07-26 16:40:00 to 2012-07-28 16:40:00

2012-07-26 16:40:00 to 2012-08-01 16:40:00

Answer 1

Here is completed version of the SQL for you :). I have created test table with the fields you have mentioned and this SQL runs perfect for me --

$sql="SELECT * FROM presents WHERE (date_from > STR_TO_DATE('".$date_from."', '%Y-%m-%d %H:%i:%s') AND date_to < STR_TO_DATE('".$date_to."', '%Y-%m-%d %H:%i:%s')) OR (date_from > STR_TO_DATE('".$date_from."', '%Y-%m-%d %H:%i:%s') AND date_to >  STR_TO_DATE('".$date_to."', '%Y-%m-%d %H:%i:%s') ) OR (date_from < STR_TO_DATE('".$date_from."', '%Y-%m-%d %H:%i:%s') AND date_to > STR_TO_DATE('".$date_to."', '%Y-%m-%d %H:%i:%s')) OR (date_from < STR_TO_DATE('".$date_from."', '%Y-%m-%d %H:%i:%s') AND date_to <STR_TO_DATE('".$date_to."', '%Y-%m-%d %H:%i:%s'))";

Attached what I get while I run the SQL in phpMyAdmin-

Let me know if you face any issue with this.

Answer 2

You should enclose date in quotes like '".$date_from."'

EDIT: try:

$sql="SELECT * FROM presents WHERE '".$date_from."' BETWEEN date_from AND date_to";

Answer 3

You're passing datetimes in form of string, hence you need to quote them with single-quote character like this:

... (date_from<'".$date_from."' AND date_to>'".$date_to."') ...

Alternatively, you can change the format of the variables you use to be a number (in YYYYMMDDHHmmss form) and then quoting is not necessary

Answer 4

Try using another syntax more like :

WHERE OrderDate BETWEEN '01/01/2006' AND '01/02/2006'

Source : http://www.demiliani.com/blog/archive/2006/01/19/3384.aspx

Answer 5

As stated in Date and Time Literals:

MySQL recognizes DATETIME and TIMESTAMP values in these formats:

• As a string in either 'YYYY-MM-DD HH:MM:SS' or 'YY-MM-DD HH:MM:SS' format. A “relaxed” syntax is permitted here, too: Any punctuation character may be used as the delimiter between date parts or time parts. For example, '2012-12-31 11:30:45', '2012^12^31 11+30+45', '2012/12/31 11*30*45', and '2012@12@31 11^30^45' are equivalent.

• As a string with no delimiters in either 'YYYYMMDDHHMMSS' or 'YYMMDDHHMMSS' format, provided that the string makes sense as a date. For example, '20070523091528' and '070523091528' are interpreted as '2007-05-23 09:15:28', but '071122129015' is illegal (it has a nonsensical minute part) and becomes '0000-00-00 00:00:00'.

• As a number in either YYYYMMDDHHMMSS or YYMMDDHHMMSS format, provided that the number makes sense as a date. For example, 19830905132800 and 830905132800 are interpreted as '1983-09-05 13:28:00'.

If you are using a string format, you must ensure that your strings are properly quoted.

Answer 6

I'm sure you need to wrap your dates in single quotes:

date_from > '".$date_from."' AND

Answer 7

You need to quote your variable.

But it is better not to concat sql, use placeholders instead.