Convert JavaScript to date object to MySQL date format (YYYY-MM-DD)

user_29538 0
Viewed 94728

I'm trying to use JavaScript to convert a date object into a valid MySQL date - what is the best way to do this?

date mysql javascript
14 Answers

Update: Here in 2021, Date.js hasn't been maintained in years and is not recommended, and Moment.js is in "maintenance only" mode. We have the built-in Intl.DateTimeFormat, Intl.RelativeTimeFormat, and (soon) Temporal instead, probably best to use those. Some useful links are linked from Moment's page on entering maintenance mode.

Old Answer:

Probably best to use a library like Date.js (although that hasn't been maintained in years) or Moment.js.

But to do it manually, you can use Date#getFullYear(), Date#getMonth() (it starts with 0 = January, so you probably want + 1), and Date#getDate() (day of month). Just pad out the month and day to two characters, e.g.:

(function() {
    Date.prototype.toYMD = Date_toYMD;
    function Date_toYMD() {
        var year, month, day;
        year = String(this.getFullYear());
        month = String(this.getMonth() + 1);
        if (month.length == 1) {
            month = "0" + month;
        }
        day = String(this.getDate());
        if (day.length == 1) {
            day = "0" + day;
        }
        return year + "-" + month + "-" + day;
    }
})();

Usage:

var dt = new Date();
var str = dt.toYMD();

Note that the function has a name, which is useful for debugging purposes, but because of the anonymous scoping function there's no pollution of the global namespace.

That uses local time; for UTC, just use the UTC versions (getUTCFullYear, etc.).

Caveat: I just threw that out, it's completely untested.

user_1572470 

function js2Sql(cDate) {
    return cDate.getFullYear()
         + '-'
         + ("0" + (cDate.getMonth() + 1)).slice(-2)
         + '-'
         + ("0" + cDate.getDate()).slice(-2);
}

const currentDate = new Date();
const sqlDate = js2Sql(currentDate);

console.log(sqlDate);

user_12996780

To convert a JavaScript date to a MySQL one, you can do:

const currentDate = new Date();
const date = currentDate.toISOString().split("T")[0];

console.log(date);

user_17839160

I'd like to say that this is likely the best way of going about it. Just confirmed that it works:

new Date().toISOString().replace('T', ' ').split('Z')[0];
user_38569410
user_1166850

Can be!

function Date_toYMD(d)
{
    var year, month, day;
    year = String(d.getFullYear());
    month = String(d.getMonth() + 1);
    if (month.length == 1) {
        month = "0" + month;
    }
    day = String(d.getDate());
    if (day.length == 1) {
        day = "0" + day;
    }
    return year + "-" + month + "-" + day;
}
user_12868650

Try this

dateTimeToMYSQL(datx) {
    var d = new Date(datx),
      month = '' + (d.getMonth() + 1),
      day = d.getDate().toString(),
      year = d.getFullYear(),
      hours = d.getHours().toString(),
      minutes = d.getMinutes().toString(),
      secs = d.getSeconds().toString();
    if (month.length < 2) month = '0' + month;
    if (day.length < 2) day = '0' + day;
    if (hours.length < 2) hours = '0' + hours;
    if (minutes.length < 2) minutes = '0' + minutes;
    if (secs.length < 2) secs = '0' + secs;
    return [year, month, day].join('-') + ' ' + [hours, minutes, secs].join(':');
  }

Note that you can remove the hours, minutes and seconds and you will have the result as YYYY-MM-DD The advantage is that the datetime entered in the HTML form remains the same: no transformation into UTC

The result will be (for your example) :

dateToMYSQL(datx) {
    var d = new Date(datx),
      month = '' + (d.getMonth() + 1),
      day = d.getDate().toString(),
      year = d.getFullYear();
    if (month.length < 2) month = '0' + month;
    if (day.length < 2) day = '0' + day;
    return [year, month, day].join('-');
  }
user_111204440
Related