Convert string to date field

[matt_a]

I have been having issues with a date field being passed into ServiceNow via a transform.

the date in the SQL table reads dd/mm/yyyy.

When the data populates into the import set, there are various issues.

I deleted the field on the import set table and ran another load of data in. This recreated the field as a string field and has corrected all of the issues I was seeing.

The problem is now transforming this into my user records.

If I set the transform map to read the field as dd-mm-yyyy i get the error:

Unable to format dd/mm/yyyy using format string dd-MM-yyyy for field u_dob

I think I am going to need a script to convert the string? But I have not been very successful to date and have tried several variations based on community posts. But nothing appears to work!

[ggg]

what is the system property set to for dates on your instance?

[matt_a]

dd-mm-yyyy

[dvp · Accepted solution]

Use the following script in field map and see if it works

var gdt = new GlideDateTime(source.u_field_name); // UPdate the field name to date field name on import set table
return gdt.getDate();

[matt_a]

Thanks once again. This worked and converted it correctly.

But I am not going to use it - I went to our dba and got them to sort out the format on the SQL table and this worked. Turns out that the column had been converted in the to display in the dd/mm/yyyy by using unions.

We have set it to the standard format for the database and this has rectified the issue.

I will keep your script as think this could come in handy.

Many thanks