Comparing MySQL data to Current Date

[JBebbington]

Hey All,

I have a database which stores the Date in 3 different fields. send_D for the day, in the format 01, send_M for the month, in the format 01, 02, 03 etc. again, and send_Y for the year, in the form 1992, 1995, 2007 etc.

What I want to know is how would I compare those 3 values to the current date, like how to do "if sql_date = CurrentDate, yada yada yada" in PHP.

Thanks,
Josh.

 

[Matthew.]

There are a couple of ways to do this, the first is a little rough by using the date function:

$str = "{$row['send_D']} {$row['send_M']} {$row['send_Y']}";

if(date('d m Y') == $str)
{
	// it is todays date.
}

A little more complicated but more useful method is to create a timestamp using strtotime.

Unfortunately strtotime will not accept the month in numeric terms so we will have to put it into text first

Example:

$months = array("January","February","March","April","May","June", 
				"July","August","September","October","November", "December");
				
$newmonth = $months[$row['send_M']];

$timestamp = strtotime($row['send_D'] . $newmonth . $row['send_Y']);

if( date('d m Y') == date('d m y', $timestamp) )
{
	// Itis todays date.
}

The good thing about that is you now have the timestamp for later use.

Personally i would recommend ditching your current database structure and using 1 field to store a timestamp or at he least NOW(). (The first being preferable).

Things to read:

http://uk2.php.net/strtotime
http://uk2.php.net/time
http://uk2.php.net/date
http://www.google.com/search?hl=en&lr=&safe=off&q=what+is+a+unix+timestamp&btnG=Search
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

 

[Kate]

:hi:

IMO it would be better to store dates in a DATE field.
In the meantime this should do the trick:

SELECT *
FROM your_table
where TO_DAYS(curdate() )=TO_DAYS( concat(send_Y,'-',send_M,'-',send_D) )

 

[Matthew.]

:hi:

IMO it would be better to store dates in a DATE field.
In the meantime this should do the trick:

And now we see the full reason why i should go off and learn more SQL syntax :hehe:

My way is messy compared to this. Then again, still stands for:

like how to do "if sql_date = CurrentDate, yada yada yada" in PHP.