View Full Version : Date conversion Problem

03-01-2012, 09:37 AM

I have saved the "to be published" date in my table in this format:
mm/dd/yyyy eg: 02/29/2012

I now want to select those records that are available to publish.

So I have this query:

$now = strtotime("now");
$sql_sup = "SELECT page_name FROM pages WHERE page_no = '$page_id' AND pub_date > $now";

But that isn't going to work so can I use the strtotime() function inside my query eg strtotime("pub_date") ?

If not is there a mysql equivilent ?



03-01-2012, 11:49 AM
You can use Mysql's str_to_date() (http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date) function to convert the string value to Mysql's special date format. After that, you can use unix_timestamp() (http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_unix-timestamp) or from_unixtime() (http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_from-unixtime) on either one of your values to get them to be the same format.

The first thing you should do, though, is convert your pub_date column to be the mysql "date" type. That's a better way to store the data and saves you a step.

Also look at the now() (http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_now) function.

Lots of stuff there, but here's the overview:

1. Convert column pub_date from varchar to date
2. Change query to this:

$sql_sup = "SELECT page_name FROM pages WHERE page_no = '$page_id' AND pub_date > NOW()";

03-01-2012, 03:55 PM

Thanks for the suggestion.

If I change the column pub_date from varchar to date,
do I need to change my format to save it ?

At the present I just have:

$Db_pub_date = '20/02/2012';

$sql = "UPDATE `pages` SET pub_date = '$Db_pub_date' WHERE page_id = '$Db_page_id' ";