Convert JavaScript to date object to MySQL date format (YYYY-MM-DD)
JavascriptMysqlDateJavascript Problem Overview
I'm trying to use JavaScript to convert a date object into a valid MySQL date - what is the best way to do this?
Javascript Solutions
Solution 1 - Javascript
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.
Solution 2 - Javascript
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.
Solution 3 - Javascript
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);
Solution 4 - Javascript
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);
Solution 5 - Javascript
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);
Solution 6 - Javascript
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);
Solution 7 - Javascript
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;
}
Solution 8 - Javascript
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);
Solution 9 - Javascript
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
Solution 10 - Javascript
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];
Solution 11 - Javascript
Take a look at this handy library for all your date formatting needs: http://blog.stevenlevithan.com/archives/date-time-format
Solution 12 - Javascript
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;
}
Solution 13 - Javascript
// 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()));
Solution 14 - Javascript
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('-');
}