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

[Ankur Bawiskar]

Hi,

What does individual value mean?

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

Blue -> date

Orange -> Time

What does the last part mean?

The incoming value doesn't look like a valid ISO8601-date format which is yyyy-MM-ddTHH:mm:ssZ

Regards
Ankur

[DanielCordick]

Yes Blue is Date, Orange is Time, the Rest i believe is i want to say milisecond and Time zone, which is useless to me, so that is why im using slice

[Jaspal Singh]

Did you try using,

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

instead of

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

[DanielCordick]

Yes, this works, The field on the table is in the correct format however the error message is still on the transform 

[Jaspal Singh]

So, just to confirm this was tried for all 3 & not just var evd?

Also, the error message that appears is 

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

which still has T in it. Which should not be the case after replace.

Are you sure you tried it for all values & not just evd from your script.