1

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.

1 Answer 1

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.