SQL date discrepancy with some dates

Question

I am working on a date problem.

So, in the app I have a table that have some user records that includes a date of birth field (datetime sql type). The problem is that for some users whose date of birth is prior to 1954, the date is not properly reflected.

For example, I have a user whose date of birth is 11/08/1920 but when I set the date of birth via server script, it ends up with the value 11/07/1920 23:23:24.

I am getting the date value from a spreadsheet and the server script looks like this:

function importDOBs(){

  var allRecs = [];

  var ss = SpreadsheetApp.openById("adfsasdfasdfasdfasdfasdf");
  var sheet = ss.getActiveSheet();

  var data = sheet.getRange(2,4,sheet.getLastRow(),2).getValues();
  for(var i=0; i<5; i++){
    var row = data[i];
    var date = row[0];
    var oldMrn = row[1];

    var query = app.models.purgedRecords.newQuery();
    query.filters.oldMrn._equals = oldMrn;
    var record = query.run()[0];
    if(record){
      var dob = new Date(date);
      record.dateOfBirth = dob;
      allRecs.push(record);
    }
  }

  app.saveRecords(allRecs);

}

These are the values in the spreadsheet (they are strings, not dates):

1954-03-04T00:00:00
2014-03-01T00:00:00
1951-10-20T00:00:00
1920-11-08T00:00:00
1938-09-27T00:00:00

However, somehow I'm always getting this:

As you see, there is a discrepancy on the dates that are prior to 1954. So far, I have tried other things such as changing this part:

if(record){
  var dob = new Date(date);
  record.dateOfBirth = dob;
  allRecs.push(record);
}

to this:

if(record){
  var dob = Utilities.formatDate(new Date(date),"GMT","yyyy-MM-dd'T'HH:mm:ss'Z'");
  var birthDate = new Date(dob);
  record.dateOfBirth = birthDate;
  allRecs.push(record);
}

and the above resulted in the same thing. I have tried other dates after 1954 and they also seem wrong. For example 05/19/1968 reflects 05/18/1968 23:00:00. So my best guess so far this has to do something with the daylight savings, perhaps?

Answer 1

The snake pit of converting dates between platforms

Try to set the values to UTC or just format them into the datetime format for MySQL

The first option as seen in the snippet below requires you to convert the format in SQL:

See this answer: MySQL yyyy-mm-ddThh:mm:ss.sssZ to yyyy-mm-dd hh:mm:ss

DATE_FORMAT(STR_TO_DATE(«string»,'%Y-%m-%dT%H:%i:%s.000Z'),'%Y-%m-%d %H:%i:%s');

//Using Z to set to UTC

let allRecs = [];
document.querySelector("pre").textContent.split("\n").forEach(function(element) {
  if (element != "") {
    const dob = new Date(element + "Z"); //Z forces te date to be in UTC with zero time offzet or: 00:00:00
    const dateOfBirth = dob;
    allRecs.push(dateOfBirth);
  }
});
console.log(allRecs);

allRecs = [];
//format to MySQL datetime format
document.querySelector("pre").textContent.split("\n").forEach(function(element) {
  if (element != "") {
    element = element.replace("T", " "); //replace T with space
    //now the string is in the datetime format for MySQL
    allRecs.push(element);
  }
});
console.log(allRecs);

<pre>
1954-03-04T00:00:00
2014-03-01T00:00:00
1951-10-20T00:00:00
1920-11-08T00:00:00
1938-09-27T00:00:00
</pre>