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' ";
Powered by vBulletin® Version 4.2.2 Copyright © 2016 vBulletin Solutions, Inc. All rights reserved.