Select a request two weeks ago

Question

In my database table, I have a field for the date (varchar field to save the date in yy-mm-dd format), now I want to select the records two weeks ago. How can i do this?

Answer 1

Implicit date arithmetic is quite flexible in MySQL. You can compare dates as strings without explicitly using CAST() or DATE() , but you trust MySQL to correctly interpret your format. Luckily for you, this will be great with yy-mm-dd .

I would recommend using BETWEEN and INTERVAL to make your request easy to read; eg:

 SELECT * FROM Holidays WHERE Date BETWEEN (NOW() - INTERVAL 14 DAY) AND NOW(); 

The only trick with BETWEEN is to first set the lower bound and the upper bound of the second; for example, if you write BETWEEN 5 AND 2 , it is always evaluated as FALSE because there is no value that can be greater than or equal to 5, and also less than or equal to 2.

Here is an example of a query in action on SQL Fiddle and a list of recognized INTERVAL expressions in MySQL.

Note that parentheses around the expression NOW() - INTERVAL 14 DAY not required, but I would recommend using them here solely for clarity. This makes the predicate clause a little easier to read in the absence of the correct syntax highlighting due to two characters.

Answer 2

Ideally, you should use date types to store dates, but if that’s not the case, you should study casting for today, and then compare.

 select * from yourtable where cast (yourdate as Date) BETWEEN Date_Add(CURDATE(), INTERVAL -21 Day) and Date_Add(CURDATE(), INTERVAL -14 Day) 

Please note: this is not tested and may require a little tweaking, but you should give a general idea of ​​what you need to do.

Also, if possible, you should really study converting the varchar field to a date field .... they have date types to prevent such an event, although I know that changing field types is not always an option.

Answer 3

you can just do with ADDDATE to get 14 days ago. compare the string with the date .

 SELECT * FROM your_table WHERE your_date >= ADDDATE(NOW(), -14) AND your_date < NOW() 

Answer 4

I use this to select past past data

 SELECT * FROM Holidays WHERE a.dDate >= DATE( NOW( ) ) - INTERVAL 14 DAY AND a.dDate <= DATE( NOW( ) ) - INTERVAL 8