CODEWITHSUNDEEP
phpmysql
user3439600
user3439600

Reputation: 149

MySql get records that are more than 7 days old (Varchar)

I have a column in my database called end_date which is a varchar and not a date, the format is 01/07/2015. I am trying to pull rows that are more than 7 days old from today's date.

I have tried the following.

$d = date('d/m/Y', strtotime(' -7 day')); // 7 days ago 24/06/2015
$sql = mysql_query("SELECT * FROM products WHERE end_date < '".$d."'");

or

$sql = mysql_query("SELECT * FROM products WHERE DATE_FORMAT(STR_TO_DATE(end_date, '%d/%m/%Y'), '%Y%m%d') < '".$d."'");

Which all return rows from yeserday, 2 days ago etc rather than only rows that are more than 7 days old. Is there a way to do this if the end_date column is varchar?

Upvotes: 1

Views: 1476

Answers (2)

ZelkiN
ZelkiN

Reputation: 1761

In your where clause try to put this:

WHERE str_to_date(end_date, '%d/%m/%Y') <= DATE_SUB(SYSDATE(), INTERVAL 7 DAY)

Upvotes: 5

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

The varchar dates are not real date once you do comparison it should always be in the form of Y-m-d format. Here what happens when you use str_to_date

mysql> select str_to_date('01/07/2015','%d/%m/%Y') as d ;
+------------+
| d          |
+------------+
| 2015-07-01 |
+------------+

The returned date is in Y-m-d format and you can use mysql functions for the comparison such as date_sub()

select * from products
where
str_to_date(end_date, '%d/%m/%Y') < date_sub(curdate(),interval 7 day);

Here how date_sub works

mysql> select date_sub(curdate(),interval 7 day) as old_day;
+------------+
| old_day    |
+------------+
| 2015-06-24 |
+------------+
1 row in set (0.00 sec)

Upvotes: 3

Related Questions