Convert date in string to date object for inserting in database

Question

I want to convert date in string to date object in javascript so that I could insert it in mysql database as DATETIME.

I try new Date(date) as work fine here, but in my js code it didn't work fine.

here is the code:

str = "Sun Sep 06 2015 11:56:23 GMT+04:30"
new Date(str);

and here is the result :

2015-09-06T11:56:23.000Z

which is different from result in here

and also I get following error when inserting it in database.

Incorrect datetime value: '2015-09-06T11:56:23.000Z' for column 'start_date' at row 1

So how can I convert date?

The MySQL field type should be a `timestamp` – Daniel Cheung Sep 06 '15 at 08:25 — Daniel Cheung, Sep 06 '15 at 08:25
@DanielCheung, I want datetime format! – user3806649 Sep 06 '15 at 08:27 — user3806649, Sep 06 '15 at 08:27

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

2

From here: Get String in YYYYMMDD format from JS date object?

Date.prototype.yyyymmdd = function() {
    var yyyy = this.getFullYear().toString();
    var mm = (this.getMonth()+1).toString(); // getMonth() is zero-based
    var dd  = this.getDate().toString();
    return yyyy + "-" + (mm[1]?mm:"0"+mm[0]) + "-" + (dd[1]?dd:"0"+dd[0]); // padding
};

Then you can:

str = "Sun Sep 06 2015 11:56:23 GMT+04:30"
new Date(str).yyyymmdd(); //returns "2015-09-06"

We can make some modifications in the original function to incorporate the time as well:

Final JavaScript

Date.prototype.YYYYMMDDhhmmss = function() {
    var YYYY = this.getFullYear().toString(),
        MM = (this.getMonth()+1).toString(),
        DD  = this.getDate().toString(),
        hh = this.getUTCHours().toString(),
        mm = this.getUTCMinutes().toString(),
        ss = this.getUTCSeconds().toString();
    return YYYY + "-" + (MM[1]?MM:"0"+MM[0]) + "-" + (DD[1]?DD:"0"+DD[0]) + " " + (hh[1]?hh:"0"+hh[0]) + ":" + (mm[1]?mm:"0"+mm[0]) + ":" + (ss[1]?ss:"0"+ss[0]);
};

Then:

str = "Sun Sep 06 2015 11:56:23 GMT+04:30"
new Date(str).YYYYMMDDhhmmss(); //returns "2015-09-06 07:26:23"

Either like this YYYY-MM-DD hh:mm:ss or YYYY-MM-DD is fine for a DateTime input in database.

edited May 23 '17 at 12:14

Community

1
1

answered Sep 06 '15 at 08:34

Daniel Cheung

4,779
1
30
63

@user3933607 I've updated my answer to incorporate time as well, which I forgot in the first place. – Daniel Cheung Sep 06 '15 at 09:51
it convert time zone, how can I convert time in the same time zone? – user3806649 Sep 06 '15 at 18:04
@user3933607 you should not be using local time, even php forces you to have a set time zone. JS is client sided, so time is relative. But if you want local time, remove UTC from the 3 Date methods, so they become `getHours` and so – Daniel Cheung Sep 06 '15 at 23:10
@user3933607 I re-read your comment. It seems I got the meaning wrong. If you want to convert the UTC (GMT+0) time zone to let's say USA time, you have to do it in PHP. You first set the timezone in your PHP document. It would automatically interpret (or you may need script) the time in your set timezone. It's best to store in UTC time or UNIX timestamp. – Daniel Cheung Sep 08 '15 at 10:52

Convert date in string to date object for inserting in database

1 Answers1

Final JavaScript

Linked