CODEWITHSUNDEEP
datejdbcgoogle-apps-scriptgoogle-cloud-sqlgoogle-forms
JDD
JDD

Reputation: 11

Google Apps Script JDBC Storing Specific Date Value from Google Sheets into Google Cloud SQL

I'm attempting to connect a spreadsheet (with the input values initially from Google Forms) to a Google Cloud SQL database, where each form submit spreadsheet row creates a record within the database. I have done it successfully before (with a form without dates), but I can't figure out how to put a specific date value (Date of Birth/birthday) into a format where I won't get a "Data truncation: Incorrect date value: '2/5/2001' for column 'birthday'" error.

The date output from google forms is "2/5/2001" for example, and I know that MySQL date format is "YYYY-MM-DD.....".
Here's the code:

function onFormSubmit(event) {
  var conn = Jdbc.getCloudSqlConnection(dbUrl, user, userPwd);
  var stmt = conn.prepareStatement('INSERT INTO persontable ' +
    '(email, firstname, lastname, birthday) values (?, ?, ?, ?)');
  stmt.setString(1, event.namedValues['Email']);
  stmt.setString(2, event.namedValues['First Name']);
  stmt.setString(3, event.namedValues['Last Name']);
  stmt.setString(4, event.namedValues['Date of Birth']);
  stmt.execute();

I know I need to convert the value from 'Date of Birth' to the correct format so that MySQL recognizes it as a DATE, I just haven't found the way to do that.

Thank you for your help.

Upvotes: 0

Views: 636

Answers (1)

JDD
JDD

Reputation: 11

Answering my own question, since I just discovered it.

var stmt = conn.prepareStatement('INSERT INTO persontable ' +
'(email, firstname, lastname, birthday) values (?, ?, ?, STR_TO_DATE(?,"%m/%d/%Y"))');

Upvotes: 1

Related Questions