How to insert a Date type variable in MySQL through JSON

Question

I've a table in the database that contains a field which is type Date. If I try to insert a date from postman through the API like this:

 {
   "registerDate" : "2014-06-02"
 }

It works, but then I try to do it from javascript with jquery taking the value from an input in the format YYYY-MM-DD or even giving to the variable the value "2014-06-02" it inserts a NULL value in the database.

 var user = new Object();
 user.registerDate = $('#register_date').val();
 createUser(url, type, JSON.stringify(user), function(user){
 });

or

 var user = new Object();
 user.registerDate = "2014-06-02";
 createUser(url, type, JSON.stringify(user), function(user){
 });

Where createUser is:

function createUser(url, type, user, success){
 $.ajax({
     url:url,
     type: 'POST',
     crossDomain : true,
     contentType : type,
     data : user
})
.done(function (data, status, jqxhr) {
      success(user);
})
.fail(function (jqXHR, textStatus) {
    console.log(textStatus);
});
}

Where is the problem if in postman works but from JavaScript it doesn't?

What happens if you use alert(user.registerDate) right after you've retrieved its value? — arielnmz
– arielnmz, Commented Jun 21, 2014 at 18:51

arielnmz · Accepted Answer · 2014-06-21 18:47:42Z

1

As a suggestion (a comment would be too big), you may want to convert your date to miliseconds using the parse method:

Date.parse('2014-06-02');

Send that as a JSON:

{'registerDate' : '1401685200'}

And parse it back in MySQL using the FROM_UNIXTIME function.

FROM_UNIXTIME(1401685200)

Or even better, you can store your date as an integer value on your database and just convert it to a date whenever you need it; since storing it as a string may cause issues with encoding.

edited Jun 21, 2014 at 18:47

answered Jun 21, 2014 at 18:38

arielnmz

9,21510 gold badges43 silver badges73 bronze badges

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

Collectives™ on Stack Overflow

How to insert a Date type variable in MySQL through JSON

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related