How to compare

OK, I have put into my mySQL database a date (the date only) as a varchar of 2. So, if, say today was May 18, 2011, I store the 18 in the varchar storedDate. Now I want to retrieve that and compare it with today. Isn’t working for me.

I am storing these broken out for a particular reason as I wanted to store the complete date (month day year) but other requirements is not allowing for that - might have been easier.

Here is one way I compare it after my getting the row from the master query

// this works
   if ($info['storedMonth'] ==  date("F"))
     echo 'Months match<br>';
       echo 'oops, months not matching???<br>?';

// this does not
    if (trim($info['storedDate']) == strtotime(date("j")))
      echo 'Dates match<br>';
      echo 'hmmmmm. dates not matching!!!<br>';

I also tried by removing the strtotime, trim and both. Not working.

Any help would be greatly appreciated.


Have you tried debugging by doing var_dump($info[‘storedDate’]) and var_dump($date(‘j’)) ?

A better way would be to store your whole date in a DATE column and use that for the comparison.

$info['date'] = '2011-05-18'; //from MySQL DATE column

if(date('j') == date('j', strtotime($info['date']))) {
  echo 'Days match';

Or you can format the components in your SQL query

   DATE_FORMAT(your_date_col, '%e') AS day_of_month,
   DATE_FORMAT(your_date_col, '%m') AS month
FROM your_table

Then day_of_month = 18 and month = 05. You can compare those components individually.

cranial-bore, thanks. You pointed me in the right direction.

This worked

    $mNewdate = $info['storedYear'].'-'.date("m",strtotime($info['storedMonth'])).'-'.$info['storedDate'];

    echo 'New Date concat: '.$mNewdate;     
    if(date('j') > date('j', strtotime($mNewdate))) 
      echo 'Days match';
      echo 'nope,no match';

I found another error - mine - in that I needed to get dates AFTER today - and this works like I want.


Glad it’s working, but I’d still recommend using a proper date column. More flexible for other things you may wish to do in future (e.g. sorting, selecting) and would use less space too.

To build upon cranial-bore’s suggestion, there are other mysql functions you might consider using to do similar.

select DAY(pubdate) from rss_news where id = 1;

id | pubdate
1 | '2011-02-13'

returns '13'

You can also use MONTH(), YEAR() and so on on date and datetime fields - overall though: avoid using varchars for dates.

Could you tell what requirements would not allow you to store the date in a date type column?

no, it returns 13

there’s a difference


Off Topic:

You’re both right. MySQL returns 13, but PHP gets ‘13’ because in PHP everything that comes from the database is a string.

Off Topic:

Ooh, that’s a good one for Cups’ thread!