I have a jQuery date that I am passiving via ajax to a php script then using the date in an sql insert into mysql database

Question

I have a javascript date that I am passing via ajax to a php script then using the date in an sql insert into mysql database. I can't figure out how to format the date so the SQL call accepts it. Can anyone help with this?

In the AJAX call I've tried passing the date object as is, converting to JSON, to UTC.

The error I get is:

Incorrect date value: 'Thu, 01 May 1902 03:01:00 GMT' for column 'uspr_dob'

This all works if I remove the date line from the SQL code. ie I update the other fields but not the date. So everything else is working except the date passing.

jQuery date formation:

    var dob = new Date(year, month, day, hours, minutes, 0, 0);

jQuery AJAX call:

    var save_result = jQuery.ajax({
                url: lb_path + "update_user.php",
                data: { 'lb_user_id'    : this.id,
                        'lb_dob'        : this.dob.toUTCString(), //toJSON
                        ... },
                type: 'POST',
                datatype: 'json',
                success: function(data, status) {
                      ...
                }, // End success
                error: function (xhr, ajaxOptions, thrownError) {
                    ...
                  }
       }); // End load the supplement list

PHP code is:

        $sql = "UPDATE userpref
        SET uspr_person_group       = \"".$lb_gender."\",
            uspr_diet_type          = (SELECT diet_id FROM diet WHERE diet_name = \"".$lb_diet."\"),
            uspr_exercise_profile   = (SELECT exprof_id FROM exercise_profile WHERE exprof_name = \"".$lb_exercise."\"),
            uspr_dob                = \"".$lb_dob."\"
        WHERE uspr_user_id = ".$lb_user_id;

score 0 · Answer 1 · edited May 23 '17 at 12:13

0

If you use YYYY-MM-dd format you will never run into any issues. It is better to convert it before passing it to the server in your case.

Please refer to Get String in YYYYMMDD format from JS date object? .

edited May 23 '17 at 12:13

Community

1
1

answered Nov 03 '16 at 04:38

Ashokkumar M. Prajapati

483
4
12

Thanks Ashok - Yes it works if I just send the YYYY-MM-dd. Though I also need the time to be stored. – Living Bliss Nov 03 '16 at 06:47

score 0 · Answer 2 · answered Nov 09 '16 at 01:43

OK - I found the problem - The database documentation showed the field in the table was DATETIME ... when actually it was DATE.

Once I spotted that and massaged the datetime string into MySQL's format it all worked beautifully!

Still I would think that there is an easier way to do this ... ie a date format in javascript that can be passed straight through AJAX, PHP then SQL and work without any formatting.

I have a jQuery date that I am passiving via ajax to a php script then using the date in an sql insert into mysql database

2 Answers2