Transforming dates in mm/dd/yy format

[mslocum]

Im trying to transform a date from a string field to a data field on the target table. The date is in mm/dd/yy format. The error im getting is this "Unable to format 05/23/20 using format string MM-dd-yyyy for field u_effdt"

Whats the best way to accomplish this given the year is using 2 characters not 4?

[Mike Patel]

maybe use split in onBefore script

var date = source.u_effdt.toString().split('/');

var newdate = date[0]+"-"+date[1]+"-20"+date[2];

target.XXX = newdate;

[hammad ul aziz2 · Accepted solution]

Import a Date Field

Fields containing dates often cause errors on import due to a mismatch in format between the date format in the data source and the date format ServiceNow expects.

To correct the format mismatch, open the Transform Map for editing. Scroll to the Field Maps related list and click the link for the date field in the Source field column.

Use the Date format field to specify the date and time format of the field in the staging table data. ServiceNow will convert the date to the format the target field expects.