convert php date to mysql format
PhpMysqlPhp Problem Overview
I have a date field in php which is using this code:
$date = mysql_real_escape_string($_POST['intake_date']);
How do I convert this to MySql format 0000-00-00 for inclusion in db. Is it along the lines of: date('Y-m-d' strtotime($date);. The reason I ask, is because I have tried variations of this and I cannot seem to get it to work. Either displays as 1970 or some other variation of that. Many thanks
Php Solutions
Solution 1 - Php
$date = mysql_real_escape_string($_POST['intake_date']);
1. If your MySQL column is DATE
type:
$date = date('Y-m-d', strtotime(str_replace('-', '/', $date)));
2. If your MySQL column is DATETIME
type:
$date = date('Y-m-d H:i:s', strtotime(str_replace('-', '/', $date)));
You haven't got to work strototime()
, because it will not work with dash -
separators, it will try to do a subtraction.
Update, the way your date is formatted you can't use strtotime()
, use this code instead:
$date = '02/07/2009 00:07:00';
$date = preg_replace('#(\d{2})/(\d{2})/(\d{4})\s(.*)#', '$3-$2-$1 $4', $date);
echo $date;
Output:
2009-07-02 00:07:00
Solution 2 - Php
This site has two pretty simple solutions - just check the code, I provided the descriptions in case you wanted them - saves you some clicks.
http://www.richardlord.net/blog/dates-in-php-and-mysql
1.One common solution is to store the dates in DATETIME fields and use PHPs date() and strtotime() functions to convert between PHP timestamps and MySQL DATETIMEs. The methods would be used as follows -
$mysqldate = date( 'Y-m-d H:i:s', $phpdate );
$phpdate = strtotime( $mysqldate );
2.Our second option is to let MySQL do the work. MySQL has functions we can use to convert the data at the point where we access the database. UNIX_TIMESTAMP will convert from DATETIME to PHP timestamp and FROM_UNIXTIME will convert from PHP timestamp to DATETIME. The methods are used within the SQL query. So we insert and update dates using queries like this -
$query = "UPDATE table SET
datetimefield = FROM_UNIXTIME($phpdate)
WHERE...";
$query = "SELECT UNIX_TIMESTAMP(datetimefield)
FROM table WHERE...";
Solution 3 - Php
You are looking for the the MySQL functions FROM_UNIXTIME()
and UNIX_TIMESTAMP()
.
Use them in your SQL, e.g.:
mysql> SELECT UNIX_TIMESTAMP(NOW());
+-----------------------+
| UNIX_TIMESTAMP(NOW()) |
+-----------------------+
| 1311343579 |
+-----------------------+
1 row in set (0.00 sec)
mysql> SELECT FROM_UNIXTIME(1311343579);
+---------------------------+
| FROM_UNIXTIME(1311343579) |
+---------------------------+
| 2011-07-22 15:06:19 |
+---------------------------+
1 row in set (0.00 sec)
Solution 4 - Php
If intake_date is some common date format you can use date()
and strtotime()
$mysqlDate = date('Y-m-d H:i:s', strtotime($_POST['intake_date']));
However, this will only work if the date format is accepted by strtotime()
. See it's doc page for supported formats.
Solution 5 - Php
Where you have a posted date in format dd/mm/yyyy, use the below:
$date = explode('/', $_POST['posted_date']);
$new_date = $date[2].'-'.$date[1].'-'.$date[0];
If you have it in mm/dd/yyyy, just change the second line:
$new_date = $date[2].'-'.$date[0].'-'.$date[1];
Solution 6 - Php
PHP 5.3 has functions to create and reformat at DateTime object from whatever format you specify:
$mysql_date = "2012-01-02"; // date in Y-m-d format as MySQL stores it
$date_obj = date_create_from_format('Y-m-d',$mysql_date);
$date = date_format($date_obj, 'm/d/Y');
echo $date;
Outputs:
01/02/2012
MySQL can also control the formatting by using the STR_TO_DATE()
function when inserting/updating, and the DATE_FORMAT()
when querying.
$php_date = "01/02/2012";
$update_query = "UPDATE `appointments` SET `start_time` = STR_TO_DATE('" . $php_date . "', '%m/%d/%Y')";
$query = "SELECT DATE_FORMAT(`start_time`,'%m/%d/%Y') AS `start_time` FROM `appointments`";
Solution 7 - Php
function my_date_parse($date)
{
if (!preg_match('/^(\d+)\.(\d+)\.(\d+)$/', $date, $m))
return false;
$day = $m[1];
$month = $m[2];
$year = $m[3];
if (!checkdate($month, $day, $year))
return false;
return "$year-$month-$day";
}
Solution 8 - Php
There is several options.
Either convert it to timestamp and use as instructed in other posts with strtotime()
or use MySQL’s date parsing option
Solution 9 - Php
If you know the format of date in $_POST[intake_date] you can use explode to get year , month and time and then concatenate to form a valid mySql date. for example if you are getting something like 12/15/1988 in date you can do like this
$date = explode($_POST['intake_date'], '/');
mysql_date = $date[2].'-'$date[1].'-'$date[0];
though if you have valid date date('y-m-d', strtotime($date)); should also work