Datetime format PHP and MySQL

Question

I have the following problem. I have a string, which contains time and date as follows: dd/MM/yy hh:mm:ss This I have in PHP. And then I have a MySQL database, which has a column of a datetime format, where I need to insert this value. Obviously the problem is, that the format is different, so instead of the actual date, it results in a field "0000-00-00 00:00:00".

Could you please help me with converting this string and then inserting it properly into MySQL? For the MySQL I use the standard INSERT INTO command.

Sounds like you'll have to parse the string and build new date from the values you get? Doesn't look all that hard. — MightyPork
– MightyPork, Commented Sep 29, 2013 at 17:12

squarecandy · Accepted Answer · 2021-07-30 12:14:07Z

7

From the DATETIME documentation:

MySQL retrieves and displays DATETIME values in YYYY-MM-DD HH:MM:SS format.

I'd use PHP's DateTime class and DateTime::createFromFormat() method, and convert the data into a MySQL-compatible date string, like so:

$date = DateTime::createFromFormat('d/m/Y H:i:s', $yourDateString);
$dateToBeInserted = $date->format('Y-m-d H:i:s');

edited Jul 30, 2021 at 12:14

squarecandy

5,1674 gold badges40 silver badges50 bronze badges

answered Sep 29, 2013 at 17:21

Amal

76.8k18 gold badges135 silver badges155 bronze badges

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

Comments

Anshad Vattapoyil · Accepted Answer · 2013-09-29 17:18:13Z

2

Write a function to convert date,

function sqldate($date)
{
   $sql_date = date('Y-m-d H:i:s',strtotime($date));
   return $sql_date;
}

And your query look like,

$query = "INSERT INTO tableName (dateColumn) VALUES('".sqldate($date)."') ";

edited Sep 29, 2013 at 17:18

answered Sep 29, 2013 at 17:12

Anshad Vattapoyil

23.5k19 gold badges90 silver badges134 bronze badges

3 Comments

Mark Baker Over a year ago

strtotime() will treat xx/yy/zz as a US format date(mm/dd/yy) rather than as UK format (dd/mm/yy)... you'll need to replace / with - for a UK date to be recognised correctly (dd-mm-yy)

Jachym Over a year ago

for some reason, if I echo the string Im getting:

Anshad Vattapoyil Over a year ago

@user2370078 Let me know what you are passing to the function?

John Woo · Accepted Answer · 2013-09-29 17:13:38Z

0

You can convert it using STR_TO_DATE() along with your INSERT statement, eg.

INSERT INTO tableName (dateColumn)
VALUES(STR_TO_DATE('dd/MM/yy hh:mm:ss','%d/%m/%y %H:%i:%s'))

SQLFiddle Demo

answered Sep 29, 2013 at 17:13

John Woo

265k70 gold badges509 silver badges500 bronze badges

Comments

CIRCLE · Accepted Answer · 2013-09-29 17:14:04Z

0

Try this:

$parsedDate = date('Y-m-d H:i:s', strtotime($yourDate));
// Insert $parsedDate into your table column Date

answered Sep 29, 2013 at 17:14

CIRCLE

4,9695 gold badges42 silver badges59 bronze badges

1 Comment

Mark Baker Over a year ago

strtotime() will treat xx/yy/zz as a US format date(mm/dd/yy) rather than as UK format (dd/mm/yy)... you'll need to replace / with - for a UK date to be recognised correctly (dd-mm-yy)

Collectives™ on Stack Overflow

Datetime format PHP and MySQL

4 Answers 4

Comments

3 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

3 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related