I have some data (insered by users) like :
(Notice that I add XX (or XXXX) when the data is not insered.)
How can I convert these String to DATETIME by using MySql syntax?
Thanks
UPDATE
$day=(is_numeric($_POST['day']) && $_POST['day'] > 0 && $_POST['day'] < 32) ? $_POST['day'] : '00';
$month=(is_numeric($_POST['month']) && $_POST['month'] > 0 && $_POST['month'] < 13) ? $_POST['month'] : '00';
$year=(is_numeric($_POST['year']) && $_POST['year'] > 1999 && $_POST['year'] < (date("Y")+1)) ? $_POST['year'] : '0000';
$data=$year."-".$month."-".$day;
$mysqlDate=date('Y-m-d', strtotime($data));
echo $mysqlDate;
There is a problem : if data is *empty-12-2010" it print 2010-11-30. Why?
If you're sure you don't want to require all the date fields, just fill them with zero if they don't exist, like this:
$month = ($_POST['month'] == '') ? '00' : $_POST['month'];
otherwise, if you're worried not about blank dates, but about non-standard input, you could do:
$month = (is_numeric($_POST['month']) && $_POST['month'] > 0 && $_POST['month'] < 13) ? $_POST['month'] : '00'
$date = date('Y-m-d G:i:s', strtotime($_POST['dataThatUserEntered']));
should do the trick, no?
Just make sure you validate the user input, otherwise you may find that $date = '1970-01-01 00:00:00 if they enter something crazy.
You can use php's strtotime function.
strtotime — Parse about any English textual datetime description into a Unix timestamp
You should check out the kind of format you can get to work with it for your string.
Mysql does the convertion automatically. You can see accepted formats here. But you should be aware of xxx and make mandatory that fields.
Well, you could use date(), using a format and string.
$mysqlDate = date( 'Y-m-d', $stringDate );
In MySQL's syntax you can use the function STR_TO_DATE():
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date
mysql> SELECT STR_TO_DATE('01,5,2013','%d,%m,%Y'); // -> '2013-05-01'
mysql> SELECT STR_TO_DATE('May 1, 2013','%M %d,%Y'); // -> '2013-05-01'
There is a problem : if data is *empty-12-2010" it print 2010-11-30.
Why? Because strtotime is reading that as 00-12-2010, which doesn't exist, so it's converting it to the day before Dec 1, which is numerically Dec 0, or in reallife, Nov 30 !