How to Transform ISO8601 date (yyyy-MM-ddTHH:mm:ssZ) to a date field in a Transform map

[Paul Ryder1]

Hi,

I have an import file that has the date fields in the ISO8601 format, example below.

2018-09-18T06:39:51Z

I've tried to update the Date format on the Field Mapping, but this does not seem to work.  I then tried the following to convert the string to a valid date, but this also does not work.

var tst = new Date('2018-09-18T06:39:51Z');

Now I could use string manipulation to change this, but I would prefer to use the built in functions where I can.

I've searched for this. but cannot find this exact question.

Many Thanks,

Paul

[ARG645 · Accepted solution]

Paul,

Its very hard to completely eliminate string manipulation in this scenario, lets just say 30% string manipulation and 70% built in Functionality will do the trick. 

After running some background scripts, this is what i found and this worked for me. In servicenow the ISO date has to be in the below format 

yyyy-mm-ddThh:mm:ss.fffZ

So your date in the example is expected to be 2018-09-18T06:39:51.000Z to make it work. 

In your field map, use the below code snippet and tweak it as you like to make it work. (Below code is tested in the background script, not in the transform map)

var source_iso = '2018-09-18T06:39:51Z'; // user source.u_coulmn to set the value here
if(source_iso.indexOf(".")==-1) 
{
  source_iso =source_iso.replace("Z", ".000Z");
}

//Built In methods
var d = new Date(source_iso);
var day= d.getUTCDate();
var month=d.getUTCMonth();
var year=d.getUTCFullYear();
var hours=d.getUTCHours();
var minutes=d.getUTCMinutes();
var seconds=d.getUTCSeconds();

// You may wanna change the below line to your specific Date tme format, here i am using YYY-MM-dd HH:mm:ss
var userDateFormat = year+"-"+month+"-"+day+" "+hours+":"+minutes+":"+seconds;
return userDateFormat 

[Michael Culhan1]

getUTCMonth() returns the zero based value of the month, I had to add 1 to it to get the calendar month.

[chandu17]

i have tried on wriing in transform script but its not working