Select query for two weeks ago

Question

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 ?

Answer 1

Implicit date arithmetic is fairly flexible in MySQL. You can compare dates as strings without explicit use of CAST() or DATE(), but you're trusting MySQL to interpret your format correctly. Luckily for you, it will do just fine with yy-mm-dd.

I would recommend using BETWEEN and 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.