Compare two dates in php

Question

i have this code here which permits me to retrieve some dates. I have a today date conserved in this way:

$time = date("d-m-Y");

My date field in the database is a varchar and it's in this format: d-m-Y

How can i compare them? i've used this but it won't work!

SELECT DATE_FORMAT('date','%d-%m-%Y'), id, WHERE date>$time;

Thanks for the help!

That's what you get when you store a **DATE** as a **VARCHAR**... Be smart, recreate the column to a `Date` type. — w00, Oct 04 '12 at 14:30
http://stackoverflow.com/questions/3651985/compare-dates-in-mysql http://stackoverflow.com/questions/1058031/mysql-compare-two-datetime-fields — long, Oct 04 '12 at 14:35
@Tao So? Your DB design is obviously flawed and that simply needs to be fixed. Or you'll keep bumping into things where you need weird, complicated solutions for something that would normally be a very easy thing to do. — w00, Oct 04 '12 at 14:37

score 2 · Accepted Answer · answered Oct 04 '12 at 14:52

If you want to compare date by fetching from database then I will suggest you to store in date format not in varchar format.

But now you can do following :

First use strtotime to convert your string to a unix timestamp. Like :

$time = strtotime( $date );

Then you use this timestamp to calculate a date in whatever format you want. To get your desired format you can do like :

$newDate = date( 'd-m-y', $time );

score 1 · Answer 2 · answered Oct 04 '12 at 14:34

1

Try using STR_TO_DATE function in the query to first convert the VARCHAR to a DATETIME value.

answered Oct 04 '12 at 14:34

Micah Carrick

9,967
3
31
43

S22h · Answer 3 · 2012-10-04T14:41:28.317

0

You are building your WHERE-Clause with the original value, try something like this:

SELECT DATE_FORMAT('date','%d-%m-%Y') AS compare_date, `id` FROM `table_name` WHERE DATE_FORMAT('date','%d-%m-%Y') > $time;

Be aware that this is a string-comparison, so it would be best to do it the other way round, e.g. %Y-%m-%d

Or use the STR_TO_DATE function as @Micah Carrick suggested.

edited Oct 04 '12 at 14:41

answered Oct 04 '12 at 14:35

S22h

553
4
16

score 0 · Answer 4 · answered Oct 04 '12 at 14:35

0

you have an extra , after id and you need a from clause as well, in the query.

SELECT DATE_FORMAT('date','%d-%m-%Y') as new_date, id from your_table WHERE new_date > $time;

answered Oct 04 '12 at 14:35

Teena Thomas

5,139
1
13
17

score 0 · Answer 5 · answered Dec 22 '14 at 05:51

      This Example Is Working 100% try Now

      $exp_date = "2006-01-16"; 

      $todays_date = date("Y-m-d"); 

      $today = strtotime($todays_date); 

      $expiration_date = strtotime($exp_date); 

      if ($expiration_date > $today) 
         { 
           $valid = "yes"; 
         } 
      else 
         { 
           $valid = "no"; 
         }

Compare two dates in php

5 Answers5