In my database table I have a field for date (varchar field to save date in yy-mm-dd format ), now I want to select records for two weeks ago.
How can i do it ?
Implicit date arithmetic is fairly flexible in MySQL. You can compare dates as strings without explicit use of
DATE(), but you're trusting MySQL to interpret your format correctly. Luckily for you, it will do just fine with
I would recommend using
INTERVAL so that your query is easily readable; for example:
SELECT * FROM Holidays WHERE Date BETWEEN (NOW() - INTERVAL 14 DAY) AND NOW();
The only trick with
BETWEEN is that you have to put the lower bound first and the upper bound second; for example, if you write
BETWEEN 5 AND 2, this always evaluates to
FALSE because there is no value that can be greater than or equal to 5 while also being less than or equal to 2.
Here's a demo of the query in action at SQL Fiddle, and a list of the recognized
INTERVAL expressions in MySQL.
Note that the parentheses around the expression
NOW() - INTERVAL 14 DAY are not required but I would recommend using them here purely for the sake of clarity. It makes the predicate clause just a little bit easier to read in the absence of proper syntax highlighting, at the expense of two characters.