1

I am trying to insert 2 dates in UK format (dd/mm/yyyy) to a MySQL database in the MySQL US date format.

I am using str_to_date. The first instance of str_to_date works as expected, but the second instance always inserts the same date as the first instance of str_to_date, even though the original dates are different.

$date_1 = "10/01/2016";
$date_2 = "16/02/2016";

$sql = "INSERT INTO customers (date_1, date_2)
VALUES (STR_TO_DATE( '$date_1', '%m/%d/%Y %h:%i' ), STR_TO_DATE( '$date_2', '%m/%d/%Y %h:%i' ))";

What is the correct way of handling multiple instances of str_to_date in a MySQL insert statement?

Thank you

Syn
  • 331
  • 1
  • 13
  • can you provide your php code? – Gouda Elalfy Dec 14 '15 at 21:04
  • You are missing a comma (`,`) in your `VALUES` list. – PM 77-1 Dec 14 '15 at 21:05
  • 2
    You're missing a comma after `'$last_name'` also missing the value for user_id – Musa Dec 14 '15 at 21:05
  • Sorry, it was a messy example, not my original work. I have updated it now. The problem still exists. – Syn Dec 14 '15 at 21:07
  • You forgot to quote the PHP dates, so they're not dates - they're mathematical division expressions. Plus, str_to_date is used to tell mysql what the ORIGINAL date value's format is. you're saying `month/day/year`, but are providing `day/month/year`. there is no month 24 or 16... – Marc B Dec 14 '15 at 21:09
  • Sorry I wrote the code out incorrectly. I have updated it now. The problem still exists. – Syn Dec 14 '15 at 21:10

1 Answers1

1

The format string of str_to_date() tells MySQL what format the first argument's date value is in. It's not how to format the value going in to mysql (e.g. the destination) format. str_to_date's output is ALWAYS a native mysql date/time value, which is yyyy-mm-dd hh:mm:ss

Since you're saying the input format is monday/day, but are providing day/month values and NO time values, you get wonky results.

Try

                         24/01/2016
STR_TO_DATE( '$date_1', '%d/%m/%Y' )

instead. Note the removal of the time format characters. Your input string has no time values at all.

mysql> select str_to_date('24/01/2016', '%m/%d/%Y %h:%i');
+---------------------------------------------+
| str_to_date('24/01/2016', '%m/%d/%Y %h:%i') |
+---------------------------------------------+
| NULL                                        |
+---------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select str_to_date('24/01/2016', '%d/%m/%Y');
+---------------------------------------+
| str_to_date('24/01/2016', '%d/%m/%Y') |
+---------------------------------------+
| 2016-01-24                            |
+---------------------------------------+
1 row in set (0.00 sec)
Marc B
  • 340,537
  • 37
  • 382
  • 468
  • Thank you very much Marc! I had incorrectly interpreted the str_to_date() feature. – Syn Dec 14 '15 at 21:16