PHP Mysql Select query for comparing dates?

[angel1987]

Hello all,

 

I am creating a script that accepts offers and its expiry date.

This expiry date is stored in column (expiry) in table.

 

There is a page that should display unexpired offers by comparing current date to the expiry date stored in database.

The date format used for storing in database and to compare is

$tdate = date("d-n-Y");

This Gives Output : 11-8-2010

 

So how do i compare current date 11-8-2010 to expiry date say 10-8-2010 in sql table?

I searched and found DATEDIFF function but i am unable to work it out. Please help with the query.

[PravinS]

You can use MYSQL STR_TO_DATE() function to convert your date in DATE format and then you can compare it will current date.

 

[abdfahim]

If you want to check the expiry date with current date, and get unexpired record, just use the following query

SELECT * from `your_table` WHERE CURDATE() < `expiry`

[angel1987]

It seems the output format of CURDATE() is not matching the output format used through PHP code (date("d-n-Y"). Hence, it is not working.

CURDATE() gives format as : 2008-11-11 whereas date function used gives format as : 11-8-2010

[abdfahim]

DATE_FORMAT(CURDATE(), '%d/%m/%Y')

[PravinS]

Try

SELECT  * FROM tblname WHERE STR_TO_DATE(expiry, '%d/%m/%Y') <= DATE(NOW());

 

[angel1987]

Thanks, i tried it but it is not working. The type of field in database for expiry is varchar.

[jdavidbakr]

Thanks, i tried it but it is not working. The type of field in database for expiry is varchar.

 

Is there a reason you're not using the DATE or DATETIME field type?  What you're trying to do will work much better and be much easier if you do.

[angel1987]

Actually the developer who have used the javascript for calender in html form has used a script that gives an out put as of date("d-n-Y");

 

And may be that's why it was never getting added in the table with the field type DATE, may be because of format mismatch. So i made it varchar.

 

So is that wrong?

[jdavidbakr]

It pretty much makes it impossible to do any queries efficiently filtering by the date column.  You can do conversions in your query, but you can't use indexes - if you have several thousand rows, for example, and want to find rows between two dates, there is no efficient way to do it - not only will MySQL have to do a full table scan, but it will also have to execute a function individually on each row to convert the varchar to a date in order to compare it to the range you are selecting.

 

I would recommend, if you can, to create a new column that is a DATE type, and run a query to populate that column with your current VARCHAR date, and change your application to store the date in that format.  Then you can set up an index on the DATE field, and it will be very easy to do the queries you are trying to do.