Unable to Insert Date into Data Extension via SSJS

Question

The Problem

I am trying to convert a date-time field that is retrieved from a WSProxy call previously. The date-time is provided to me in the format of YYYY-MM-DDTHH:mm:ss.sss, trying to insert this value into a data extension where the field type is of type Date will fail but will as expected successfully insert into Text fields.

When I test and try to insert Date.now() into a Date data extension field, it works without issue. A Date.now() value is also in milliseconds (epoch).

Because of this; I thought that I could simply use Date.parse(string) which will convert a date-time string into its respective milliseconds' value (epoch). Unfortunately, this seems to fail.

My Sample Code

<script runat="server">
  Platform.Load("Core", "1.1.1");

  var myDE = DataExtension.Init("my_test_de");
  var status = "complete"; // string
  var start_time = Date.now(); // EPOCHmilliseconds
  var complete_time = "2022-05-19T07:28:28.397" // string ISO
  var date_complete_time = Date.parse(complete_time); // converts ISOstring to EPOCH

  myDE.Rows.Add({Status: status, StartTime: start_time, CompleteTime: complete_time, CompleteTimeDate: date_complete_time});
</script>

The Question: How do I parse and insert an ISO Date-Time field retrieved in SSJS so that it will be accepted as input for a Date Field in my Data Extension?

Thank you.

Answer 1

As you noticed the date field needs to be in ISO 8601; however your example has it statically coded, so I am assuming that you got the date-time from a data extension or as a result of a SOAP API call.

I'm not sure if this is a bug with the SSJS or if it's intended given the version of SSJS that's used. When retrieving dates via API, most of the time I end up with an array of objects.

var array = [{"Status":1,"StatusMessage":"Complete","StartTime":"2022-05-19T07:28:01.561","CompletedTime":"2022-05-19T07:28:28.397"}, {"Status":1,"StatusMessage":"Complete","StartTime":"2022-05-19T07:28:01.561","CompletedTime":"2022-05-19T07:28:28.397"}]  

I iterate through the array:

for (var i = 0; i < array.length; i++) {
        var status = array[i].Status;
        var status_message = array[i].StatusMessage;
        var start_time = array[i].StartTime;
        var completed_time = array[i].CompletedTime;
    }

Now there is a problem here; start_time and completed_time were both provided by the API in ISO format, however, when doing this assignment, SSJS will change it to from: 2022-05-19T07:28:01.561 to: 5/19/2022 7:28:01 AM. I am not sure why this happens but you can verify that it does by trying to insert either into a Text attributed field in a data extension.

The ONLY way I could find to persis milliseconds and preserve the original integrity of the date-time is to do the following:

        var completed_time = Stringify(history[n].CompletedTime);
        completed_time = completed_time.replace(/[\"\']/gi, "");

Stringify in this case will encapsulate the entire result for CompletedTime in double-quotes; thus preserving the ISO format. However, it's not encapsulated in double-quotes so we do a `replace to remove the double-quotes.

The resulting value for completed_time will be entered in ISO format into a Text attribute in a DE and will also be accepted into a Date attribute in a DE.

Alternatively, you can write your own toISOString function but unfortunately when you do new Date(string), the milliseconds are lost.