Writing usable dates into MySQL from Google Sheet

Question

I have a script writing data from a google sheet into a Google Cloud MySQL table but want to have dates that are usable by Data Studio. I've tried using Date, Datetime, and Timestamps but I get the same error:

Attempted to update TABLE sampledata in DB TestData, but the following error was returned: Exception: Data truncation: Incorrect datetime value: 'Tue Jan 01 2019 00:00:00 GMT-0500 (Eastern Standard Time)' for column 'dateordered' at row 1

I've tried using the ToDate function in Data Studio to change it into a date, but it still won't recognize it. I feel like i'm bashing my head against a wall, and wanted to get some fresh ideas on how to do this. This is the script that is running in GAS to write into the MySQL table.

function LessCo1() {

  var sheetName = 'Sheet';

  var dbAddress = 'Address';
  var dbUser = 'User';
  var dbPassword = 'Pass';
  var dbName = 'TestData';
  var dbTableName = 'sampledata';

  var dbURL = 'jdbc:mysql://' + dbAddress + '/' + dbName;

  var sql = "INSERT INTO " + dbTableName + " (name, productkey, businesspartner, dateordered, price, quantity, promisedate, deliverydate, deliveredqty, market, doctype, docstat, docno, orderref) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";
  
  var maxRecordsPerBatch = 50;

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName(sheetName);
  
  var sheetData = sheet.getRange(1, 1, 10000, 14).getValues();

  var dbConnection = Jdbc.getConnection(dbURL, dbUser, dbPassword);

  var dbStatement = dbConnection.prepareCall(sql);
  
  var name, productkey, businesspartner, dateordered, price, quantity, promisedate, deliverydate, deliveredqty, market, doctype, docstat, docno, orderref;

  var recordCounter = 0;
  var lastRow;

  dbConnection.setAutoCommit(false);

  for (var i = 1; i <= 9000; i++) 
  {

    lastRow = (i + 1 == sheetData.length ? true : false);
    
    name = sheetData[i][0];
    productkey = sheetData[i][1];
    businesspartner = sheetData[i][2];
    dateordered = sheetData[i][3];
    price = sheetData[i][4];
    quantity = sheetData[i][5];
    promisedate = sheetData[i][6];
    deliverydate = sheetData[i][7];
    deliveredqty = sheetData[i][8];
    market = sheetData[i][9];
    doctype = sheetData[i][10];
    docstat = sheetData[i][11];
    docno = sheetData[i][12];
    orderref = sheetData[i][13];
    
    
    dbStatement.setString(1, name);
    dbStatement.setString(2, productkey);
    dbStatement.setString(3, businesspartner);
    dbStatement.setString(4,dateordered);
    dbStatement.setString(5, price);
    dbStatement.setString(6, quantity);
    dbStatement.setString(7, promisedate);
    dbStatement.setString(8, deliverydate);
    dbStatement.setString(9, deliveredqty);
    dbStatement.setString(10, market);
    dbStatement.setString(11, doctype);
    dbStatement.setString(12, docstat);
    dbStatement.setString(13, docno);
    dbStatement.setString(14, orderref);

    dbStatement.addBatch();

    recordCounter += 1;

    if (recordCounter == maxRecordsPerBatch || lastRow)
    {
      try {
        dbStatement.executeBatch();
      }
      catch(e)
      {
        console.log('Attempted to update TABLE `' + dbTableName + '` in DB `' + dbName + '`, but the following error was returned: ' + e);
      }

      if (!lastRow)
      { 
        dbStatement = dbConnection.prepareCall( sql );
        recordCounter = 0;
      }
    }
  }

  dbConnection.commit();
  dbConnection.close();
}

Answer 1

The date format you are passing to the table is incorrect. The error you are receiving tells you that the string 'Tue Jan 01 2019 00:00:00 GMT-0500 (Eastern Standard Time)' is not the datetime object it expects for dateordered column.

So, your date needs to be converted to a DATETIME - format: YYYY-MM-DD HH:MI:SS object. To get there you need to use string manipulation and remove unnecessary parts. For example:

 var myDate = 'Tue Jan 01 2019 00:00:00 GMT-0500 (Eastern Standard Time)'.split(" ").splice(0,6);
 var timeZone = myDate.slice(-1)[0].split("GMT");
 timeZone[0] = "GMT";
 console.log(timeZone);
 // ["GMT", "-0500"]
 myDate = myDate.slice(0,5).join(" ");
 console.log(myDate);
 // "Tue Jan 01 2019 00:00:00"

Now that you parted with the needless let's put that date into the SQL table shall we?

The following is an example with a table of one column of datetime type, and you should adapt it to your JS code in your var sql.

CREATE TABLE foo_bar (
    dateordered datetime
);
INSERT INTO foo_bar (dateordered) VALUES (CONVERT_TZ(STR_TO_DATE(myDate,"%a %b %d %Y %T"),timeZone[0],timeZone[1]));
SELECT * FROM foo_bar ;
// Output: "2018-12-31 20:00:00" 

You now have a datetime object in your DB table that was converted to GMT timezone, so keep that in mind when reading from the db, in some cases you may want to use CONVERT_TZ to localize it.

Use STR_TO_DATE(str,format) to convert dates from strings to SQL datetime.

From the documentation: "It takes a string str and a format string format. STR_TO_DATE() returns a DATETIME value if the format string contains both date and time parts, or a DATE or TIME value if the string contains only date or time parts. If the date, time, or datetime value extracted from str is illegal, STR_TO_DATE() returns NULL and produces a warning."