PHP/MySql How to convert dateTime from STRING to DATE

Question

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.

@ peterm See the query given in the question.

James
– James

2014-01-15 06:57:24 +00:00
Commented Jan 15, 2014 at 6:57 — James
– James, Commented Jan 15, 2014 at 6:57

user2936213 · Accepted Answer · 2014-01-15 06:46:21Z

2

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.

answered Jan 15, 2014 at 6:46

user2936213

1,0111 gold badge8 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

James Over a year ago

Then what should I do if I want to insert AM Or PM

user2936213 Over a year ago

you have to change it to varchar then.

James Over a year ago

Your statement returns 0000-00-00 00:00:00 when I run.

user2936213 Over a year ago

When you are already having this string in date time format with am/pm then why you want it to convert again into datetime?

James Over a year ago

I wanted to convert because I used the datatype as datetime now I changed that to varchar but still I have problem with your code. If I pass 15/1/2014 12:49:53 it returns 1970-01-01 01:00:00 AM.

|

Jenz · Accepted Answer · 2014-01-15 06:48:07Z

1

Try this code:

$timestamp = strtotime($added);

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

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

edited Jan 15, 2014 at 6:48

answered Jan 15, 2014 at 6:42

Jenz

8,3718 gold badges50 silver badges83 bronze badges

Collectives™ on Stack Overflow

PHP/MySql How to convert dateTime from STRING to DATE

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related