I am trying to take data from xls and upload it to database(sql), in the xls I have a date column in format dd-mm-yyyy, but it is somehow getting converted to 5 digit number and sql is throwing error "Incorrect date value: '43831' for column 'date_of_birth' at row 1".
JS format : date; SQL format : date;
Excel stores it's dates as the number of days since Jan 1 1900, Javascript uses a system similar to unix time, 1 Jan 1970, but use milliseconds instead of days.
So using this info, a quick conversion should look something like ->
const excelEpoc = new Date(1900, 0, -1).getTime();
const msDay = 86400000;
function excelDateToJavascript(excelDate) {
return new Date(excelEpoc + excelDate * msDay);
}
console.log(excelDateToJavascript(43831));