CODEWITHSUNDEEP
phpmysqlsqldatetime
James
James

Reputation: 2964

PHP/MySql How to convert dateTime from STRING to DATE

I'm trying to insert some data to my database. The data contains also one date and time. So I need to insert that by converting into MySql DateTime format. I tried by using STR_TO_DATE, But it returns Date and Time Without AM OR PM.

Code Executed :

$sql = "INSERT into `projtemp` (`user_id`,`temp_name`,`processes`,`added`) VALUES('$uid','" . clean_data($tempName) . "','" . serialize($process) . "',STR_TO_DATE('$added', '%e/%c/%Y %r'))";
$result = $mysqli->query($sql) or die("Sql Error :" . $mysqli->error);

Query :

INSERT INTO `projtemp` (`user_id`,`temp_name`,`processes`,`added`) VALUES('0000000001','dsfwfwrfer','a:5:{i:0;s:11:"rgergergerg";i:1;s:29:"software requirement analysis";i:2;s:8:"regergre";i:3;s:16:"eqgerrrrrrrrrger";i:4;s:14:"ergergggggrreg";}',STR_TO_DATE('15/1/2014 11:34:21 AM', '%e/%c/%Y %r'))

Resultant Row :

enter image description here

I referred This Site, In that they says %r appends AM Or PM to Time But it didn't worked for me. Can anyone please help me to solve this issue.

Upvotes: 1

Views: 5760

Answers (2)

Jenz
Jenz

Reputation: 8369

Try this code:

$timestamp = strtotime($added);

Then convert it to timestamp/datetime format using date(),

date("Y-m-d H:i:s A", $timestamp);

Upvotes: 1

user2936213
user2936213

Reputation: 1011

For date without am/pm: 

$datetime = date("Y-m-d H:i:s", strtotime($added));

With am/pm: 

$datetime = date("Y-m-d H:i:s A", strtotime($added));

Finally your SQL stament will be: 

$sql = "INSERT into `projtemp` (`user_id`,`temp_name`,`processes`,`added`) VALUES('$uid','" . clean_data($tempName) . "','" . serialize($process) . "','".$datetime."')";

For more details on date function refer: http://www.php.net/manual/en/function.date.php
*Note: If your column is of dataype datetime it won't insert am/pm with the date.

Upvotes: 2

Related Questions