Removing a character from a string value before transform

[DanielCordick]

Hi All,

As the title states, on my import set table i have 3 string fields that are filled with a date, time, and timezone. The format looks like this and ServiceNow does not like the format, the import errors out, 

 "2020-03-02T09:29:13.311-08:00"

I essentially need it to look like this so serviceNow can update a date time field during the transform to my table.

 2020-03-02 09:29:13

source fields are. u_enter_date, u_create_date, u_effective_date

on my user table i created the target fields for simplicity, u_enter_date, u_create_date, u_effective_date

i believe i could use slice to remove the end and be left with :2020-03-02T09:29:13 but how do i remove the "T"

code below does not work:

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {


var a = source.u_event_date.slice(0, 19);
var b = source.u_create_date.slice(0, 19);
var c = source.u_effective_date.slice(0, 19);

var evd = a.replace("T", "");
var efd = b.replace("T", "");
var cd = c.replace("T", "");

target.u_event_date = evd;
target.u_effective_date = efd;
target.u_create_date = cd;


})(source, map, log, target);

error message i get on my import table

Unable to format 2020-03-02T00:00:00 using format string yyyy-MM-dd hh:mm:ss for field u_effective_date

[Jaspal Singh · Accepted solution]

Can you try using replace in below format,

var evd = a.replace(/T/g, '');

[DanielCordick]

I ended up writing a script and using var evd = a.replace(/T/g, ''); on the source field directly.

No errors present when importing data. Field Values update as they should,

thanks for all your help

[Ankur Bawiskar]

Hi,

So you have 3 incoming values in format as -> 2020-03-02T09:29:13.311-08:00 that needs to be mapped into 3 fields

All 3 target fields are of same type? Are those date type or date/time?

Regards
Ankur

[DanielCordick]

they are all date/time on the target table