Date to sql db fails

Question

I have the below datepciker:

<script>
    $(function() {
        $( "#datepicker" ).datepicker({
            dateFormat: 'mm-dd-YYYY'
        }).val(getTodaysDate(0)); // For current date

    });

  function getTodaysDate (val) {
    var t = new Date, day, month, year = t.getFullYear();
    if (t.getDate() < 10) {
        day = "0" + t.getDate();
    }
    else {
        day = t.getDate();
    }
    if ((t.getMonth() + 1) < 10) {
        month = "0" + (t.getMonth() + 1 - val);
    }
    else {
        month = t.getMonth() + 1 - val;
    }

    return (day + '/' + month + '/' + year);
   }
</script>

The input:

<input type="text" id="datepicker" name="datepicker">

And here I'm trying to insert the date to my MySQL db. There is no error message, the insert command runs OK, but the date field in my db is empty/NULL.

$date = $_POST['datepicker'];
$sql="INSERT INTO table (dt) VALUES ('$date')";

Is this some value type issue or what? The dt column in my db is DATE.

*UPDATE @Dwza: So I modified the script:

dateFormat: 'YYYY-mm-dd'

and

return (year + '-' + month + '-' + day);

And the INSERT suggested by the link you mentioned:

$parts = explode('-', $_POST['datepicker']);
$date  = "$parts[2]-$parts[0]-$parts[1]";
$sql="INSERT INTO transfer (dt) VALUES ('$date')";

Still no go.

**SOLVED:

$date  = "$parts[0]-$parts[1]-$parts[2]";

unlike jQuery or JavaScript, `$_POST['datepicker'];` PHP catches on the basis of `name`, not `id`!! :) — NoobEditor, Jul 11 '14 at 11:24
I've just added `name` as well, check Daan's answer, but still the same. — fishmong3r, Jul 11 '14 at 11:25
What value do you get in `$_POST['datepicker']`? If it contains something that looks like a date then maybe the format is different (e.g. yyyy-mm-dd vs. mm/dd/yyy) and you'll either have to change the date picker format, or change db locale. — Petr 'PePa' Pavel, Jul 11 '14 at 11:26
@Petr'PePa'Pavel I alredy mentioned that `dt` is `DATE()`. `$_POST['datepicker']` returns `11/07/2014` as expected. — fishmong3r, Jul 11 '14 at 11:29
an other question, what do you expact to do with the `val`in `getTodaysDate` ? i mean if you pass a 20, this will propably causes some wired output ^^ — Dwza, Jul 11 '14 at 11:59
i added a update to my post. no need to explode ^^ or leave return like it was in beginning and than explode in php to sort it in the right date string format — Dwza, Jul 11 '14 at 12:28

score 2 · Accepted Answer · edited May 23 '17 at 12:11

2

in case that you try to insert a string (date) into a column from type DATE, you need a valid date format string.

this 11/07/2014 seems to be a german date format :D

check this answere

Quote:

MySQL recognizes DATE values in these formats:

As a string in either 'YYYY-MM-DD' or 'YY-MM-DD' format. A “relaxed” syntax is permitted: Any punctuation character may be used as the delimiter between date parts. For example, '2012-12-31', '2012/12/31', '2012^12^31', and '2012@12@31' are equivalent.

As a string with no delimiters in either 'YYYYMMDD' or 'YYMMDD' format, provided that the string makes sense as a date. For example, '20070523' and '070523' are interpreted as '2007-05-23', but '071332' is illegal (it has nonsensical month and day parts) and becomes '0000-00-00'.

As a number in either YYYYMMDD or YYMMDD format, provided that the number makes sense as a date. For example, 19830905 and 830905 are interpreted as '1983-09-05'.

this should solve your problem :)

change this

return (day + '/' + month + '/' + year);

to this

return (month + '/' + day + '/' + year);

or

return (year + '-' + month + '-' + day);

EDIT for Q:

You don't have to explode the data, its already valid date format after you change the retrun like i sayed above.

you could leave it like you have on beginning and than use explode.

edited May 23 '17 at 12:11

Community

1
1

answered Jul 11 '14 at 11:33

Dwza

6,494
6
41
73

I haven't realize that... So if in the script the format is set `dateFormat: 'mm-dd-YYYY'` then why it's actually `dd/mm/YYYY`? – fishmong3r Jul 11 '14 at 11:47
1

you have `return (day + '/' + month + '/' + year);` this is german ^^ – Dwza Jul 11 '14 at 11:48
sorry I was overlooking his getTodaysDate() function call. – VMai Jul 11 '14 at 11:52
no problem @VMai, happens :) i realized it when he posted his output ^^ – Dwza Jul 11 '14 at 11:52
`$date = "$parts[2]-$parts[0]-$parts[1]";` was missed. It's OK now. Thank you again. – fishmong3r Jul 11 '14 at 12:03
One more interesting stuff. If the form loads the datepicker shows the current date in format `YYYY-mm-dd` like I want it, but if I pick some date from the calendar it shows up like this `mm/dd/YYYY`. Any clue? – fishmong3r Jul 11 '14 at 12:47

score 1 · Answer 2 · answered Jul 11 '14 at 11:18

1

You have to specify a name datepicker in your input field like so:

 <input type="text" id="datepicker" name="datepicker">

answered Jul 11 '14 at 11:18

Daan

12,099
6
34
51

I thought it detects it by the `id` rather than the `name`. Anyway I've just added a `name` as well, but I still have the very same issue. – fishmong3r Jul 11 '14 at 11:24

Date to sql db fails

2 Answers2