Inserting variable into MySQL datetime

Question

I am getting the date and time from user input and then trying to insert the given date and time into a datetime column in MySQL. This it doesn't seem to be working. However, when I insert NOW() it works fine.

I have the variables: $year, $month, $date, $hour, and $minute.

And this is what I am doing:

    $i_date = "$year";
    $month= sprintf("%02s", $month); // These add a leading zero to numbers less than 10
    $i_date .= "$month";
    $date = sprintf("%02s", $date);
    $i_date .= "$date";
    $hour = sprintf("%02s", $hour);
    $i_date .= "$hour";
    $minute = sprintf("%02s", $minute);
    $i_date .= "$minute";
    $i_date .= "00"; // These are for seconds.

This gives the me the correct format for the date. I have checked it by echoing it out and then trying to manually insert into the database and it works.

But when I try to insert $i_date into the datetime column via PHP, it does not accept it.

How can I correct this?

Could you show the exact way you are trying to insert this into your database? Maybe try adding some : and . or something like that here and there? — kapa
– kapa, Commented Jun 3, 2013 at 19:07
Thanks everyone, but a reply below has fixed the issue. It was just an issue due to formatting. — user2388643
– user2388643, Commented Jun 3, 2013 at 19:21

silkfire · Accepted Answer · 2013-06-03 19:26:59Z

2

I think you're missing the hyphens and the colons. Format should be:

Y-m-d H:i:s // PHP format

For example, the date time right now:

2013-06-03 21:09:00

And thus, with your code:

$i_date = sprintf('%d-%02d-%02d %02d:%02d:00', $year, $month, $date, $hour, $minute);

edited Jun 3, 2013 at 19:26

answered Jun 3, 2013 at 19:07

silkfire

26.3k17 gold badges92 silver badges116 bronze badges

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

1 Comment

user2388643 Over a year ago

This worked. I think it was just that my formatting was not correct. Even though, the format I mentioned above worked when I inserted it manually. Nonetheless, thank you so much. Greatly appreciated.

Marc B · Accepted Answer · 2013-06-03 19:12:46Z

1

You could simplify all of those repeated sprintfs:

$mysql_date = sprintf('%04d-%02d-%02d %02d:%02d:%02d', $year, $month, $day, $hour, $minute, $second);

answered Jun 3, 2013 at 19:12

Marc B

362k44 gold badges433 silver badges508 bronze badges

Comments

Explosion Pills · Accepted Answer · 2013-06-03 19:08:05Z

0

The correct format for the date is YYYY-MM-DD HH:II:SS, so you assuming $year is guaranteed to be four digits, you should update it to

 $i_date = "$year-$month-$date $hour:$minute:00";

after all of the 0 padding is done.

answered Jun 3, 2013 at 19:08

Explosion Pills

192k56 gold badges341 silver badges417 bronze badges

Collectives™ on Stack Overflow

Inserting variable into MySQL datetime

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related