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

Question

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

possible duplicate of [Convert JS date time to MySQL datetime](http://stackoverflow.com/questions/5129624/convert-js-date-time-to-mysql-datetime) — Gajus, Dec 01 '13 at 12:01

Afanasii Kurakin · Answer 1 · 2021-12-29T16:57:13.757

160

To get the date:

const date = new Date().toJSON().slice(0, 10)
console.log(date) //2015-07-23

For datetime:

const datetime = new Date().toJSON().slice(0, 19).replace('T', ' ')
console.log(datetime) //2015-07-23 11:26:00

Note that the resulting date/datetime will always be in the UTC timezone.

edited Dec 29 '21 at 16:57

answered Feb 22 '13 at 10:18

Afanasii Kurakin

3,330
2
24
26

2

OP wants a MySQL DATE object, not a DATETIME, so you need "/T.*/" instead of "'T'". apart from that, wondering why this is not the most upvoted answer? – xShirase Sep 13 '14 at 17:15
2

Why Not this? (new Date()).toISOString().substring(0, 10) – crickeys Jan 20 '15 at 22:57
1

A slight variation on the same theme: ``(new Date()).toISOString().split(/[T\.]/).slice(0,2).join(' ')`` – Andrew Eddie Apr 19 '16 at 23:33
5

Just a note that this won't work for years over 9999 ;) – Kuba Holuj Aug 30 '17 at 21:59
3

When i did this (new Date(str_date)).toISOString().substring(0, 10) the date became day before the str_date. not accurate when parsing from string to date – Shift 'n Tab Nov 07 '17 at 23:41
1

@ShiftN'Tab yes, you need to be careful with that, because the result is in the UTC timezone – Afanasii Kurakin Nov 09 '17 at 04:34
3

@KubaHoluj I'll make sure to add a note about that in the comments for the poor sod reviewing my code in the year 10000 – serakfalcon Mar 12 '18 at 05:44
1

Best and most simplest answer i've seen so far. – Richard Skinner Feb 19 '20 at 08:23
What will happen in the year 10000 :O ;) – Roel Feb 28 '20 at 12:33

T.J. Crowder · Accepted Answer · 2021-12-28T11:24:09.147

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.

MomentJS also now "a legacy project" in their own words https://momentjs.com/docs/#/-project-status/ so best is actually to directly use `Date`. — Jim Driscoll, Dec 28 '21 at 11:12

score 11 · Answer 3 · edited Dec 29 '21 at 14:35

11

The shortest version of this answer by Chris Mayhew:

/**
 * MySQL date
 * @param {Date} [date] Optional date object
 * @returns {string}
 */
function mysqlDate(date = new Date()) {
  return date.toISOString().split('T')[0];
}

const date = mysqlDate();
console.log(date);

edited Dec 29 '21 at 14:35

double-beep

5,031
17
33
41

answered Dec 05 '14 at 12:14

Vitaly Sivkov

407
8
14

score 8 · Answer 4 · edited Dec 29 '21 at 14:38

8

Just edit the string instead:

const date = new Date();
const myDate = date.toISOString().replace("T", " ");
const myDateString = myDate.substring(0, myDate.length - 5);

console.log(myDateString);

edited Dec 29 '21 at 14:38

double-beep

5,031
17
33
41

answered Jul 12 '12 at 14:10

Chris Mayhew

97
1
1

4

There are quite a few things wrong here. Do not redeclare `var`. Read about "hoisting". Furthermore, this is sufficient, `new Date().toISOString().slice(0, 10)`. – Gajus Dec 01 '13 at 12:00
@Gajus for me `Date().toISOString().slice(0, 10)` was not sufficient fails with several dates - wrong by 1 day for 7th June 1979 for instance – Tin Bum Mar 10 '14 at 01:45
1

-1 when you use `toISOString()` you are converting the date from the timezone shown into UTC time. So for example if the web page you are looking at is on GMT-6 and showing a GMT-6 time and your browser is running on a computer that is GMT-5 then when you use `toISOString()` you will now have a UTC time that 5 hours off the webpage instead of 6 – gillyspy Jul 22 '14 at 15:47

score 7 · Answer 5 · edited Dec 29 '21 at 14:39

7

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);

edited Dec 29 '21 at 14:39

double-beep

5,031
17
33
41

answered Mar 29 '12 at 02:43

KMT

71
1
1

score 6 · Answer 6 · edited Dec 29 '21 at 14:41

6

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);

edited Dec 29 '21 at 14:41

double-beep

5,031
17
33
41

answered Oct 06 '17 at 09:14

Jan Jůna

4,965
3
21
27

score 3 · Answer 7 · answered Jun 22 '10 at 22:22

A bit of a typo in the first example, when a day has a length less than 1 it is adding the month instead of the day to the result.

Works great though if you change:

    if (day.length == 1) {
        day = "0" + month;
    }

to

    if (day.length == 1) {
        day = "0" + day;
    }

Thanks for posting that script.

The corrected function looks like:

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;
}

score 3 · Answer 8 · edited Dec 29 '21 at 14:42

3

Object.defineProperties(Date.prototype, {
  date: {
    get: function() {
      return this.toISOString().split('T')[0];
    }
  },
  time: {
    get: function() {
      return this.toTimeString().match(/\d{2}:\d{2}:\d{2}/)[0];
    }
  },
  datetime: {
    get: function() {
      return this.date + " " + this.time
    }
  }
});

const date = (new Date).datetime
console.log(date);

edited Dec 29 '21 at 14:42

double-beep

5,031
17
33
41

answered Jul 25 '15 at 14:47

bortunac

4,642
1
32
21

score 2 · Answer 9 · edited May 23 '17 at 12:02

I needed this for a filename and with the time in the current timezone.

const timezoneOffset = (new Date()).getTimezoneOffset() * 60000;

const date = (new Date(Date.now() - timezoneOffset))
    .toISOString()
    .substring(0, 19)
    .replace('T', '')       // replace T with a space
    .replace(/ /g, "_")     // replace spaces with an underscore
    .replace(/\:/g, ".");   // replace colons with a dot

Source

score 1 · Answer 10 · answered Oct 13 '20 at 22:45

1

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];

answered Oct 13 '20 at 22:45

Bennybear

335
2
13

score 0 · Answer 11 · answered May 23 '12 at 20:58

// function
getDate = function(dateObj){
    var day = dateObj.getDay() < 9 ? '0'+dateObj.getDay():dateObj.getDay();
    var month = dateObj.getMonth() < 9 ? '0'+dateObj.getMonth():dateObj.getMonth();
    return dateObj.getFullYear()+'-'+month+'-'+day;
}

// example usage
console.log(getDate(new Date()));

// with custom date
console.log(getDate(new Date(2012,dateObj.getMonth()-30,dateObj.getDay()));

score 0 · Answer 12 · answered Feb 17 '10 at 11:20

0

Take a look at this handy library for all your date formatting needs: http://blog.stevenlevithan.com/archives/date-time-format

answered Feb 17 '10 at 11:20

JoseMarmolejos

1,760
1
17
34

score 0 · Answer 13 · answered Jul 05 '19 at 13:20

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('-');
  }

score 0 · Answer 14 · edited Oct 29 '12 at 03:46

0

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;
}

edited Oct 29 '12 at 03:46

Jason Sturges

15,855
14
59
80

answered Mar 22 '12 at 19:59

Samuel Peixoto

1

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

14 Answers14

Source

Linked