Google script formatDate issue using getScriptTimeZone

Question

I'm using JDBC data in a webapp using Google Script. It pulls data from a db, then displays it in html. I thought using Utilities.formatDate to convert jdbc result dates into formatted strings would work.

In my Code.gs file, I'm using:

var e_result = SQLstatement.executeQuery("select * from events...");
var resultsArray = [];
while(e_result.next()) {
  resultsArray.push({'start_dte': Utilities.formatDate(new Date(e_result.getString('start_dte')), Session.getScriptTimeZone(), "MMM dd")}); 
  // test formatDate
  Logger.log("pre format date="+e_result.getString('start_dte'));
  Logger.log("post format date="+Utilities.formatDate(new Date(e_result.getString('start_dte')), Session.getScriptTimeZone(), "MMM dd"));
}

This outputs into Logger:

[15-08-21 04:32:50:580 PDT] pre format date=2015-07-23 00:00:00.0
[15-08-21 04:32:50:581 PDT] post format date=Dec 31

The pre format date is the correct one found in the db. But formatDate code isn't giving the same date string.

I've used "GMT-08:00" with exact same results as the "post format date" above (thinking it was a timezone issue). What am I missing??

Answer 1

The reason why post format date is being returned as Dec 31 is because if a wrong date or a date in the wrong format is passed to the Date() function it returns the value of Wed Dec 31 1969 00:00:00. Your 'start_dte' value needs to be in a particular format to be passed to the Date() function so that it can be parsed correctly and hence be put in your desired format. Below is a small sample to make this more clear:

function testDate(){

  var start = "Aug 7 2015 00:00:00 GMT+0100";
  var testDate = Utilities.formatDate(new Date(start), "GMT-08:00", "MMM-dd-yyyy");

  Logger.log(testDate);
}

Output:

[15-08-21 12:46:20:285 PDT] Aug-06-2015

The start could also be just Aug 7 2015 00:00:00 and it will work. However, whenever a value in a format that the Date() function does not expect is passed, it return the value of Dec 31 1969.