Unable to import date/time field data correctly from import set

[kBahu]

I am importing data for a date/time field through import sets into servicenow. Target filed is a date/time field on the sys_user table. 

1. target field format is a date/time field, date format on user table is default - (system dd-mm-yyyy), time zone is default - System Europe/London

2. time format set for the field on transform map is dd-MM-yyyy HH:mm:ss

3. in import set (csv file), date field data is in format - dd/mm/yyyy HH:mm:ss 

4.onBefore script - 

var sourceDate = source.u_endtime;
var gdt1 = new GlideDateTime();
gdt1.setDisplayValue(sourceDate,'dd-MM-yyyy HH:mm:ss');
target.u_ooo_end_date = gdt1.getDisplayValue();

Issue is -

1. receiving error while importing data as - 'Unable to format 21/05/2020 05:00:00 using format string dd-MM-yyyy HH:mm:ss for field u_ooo_end_date'

2. When date in import file is in format - MM/DD/YYYY HH:mm:ss AM/PM , it is mapping it correctly

but when date in import file is in format - DD/MM/YYYY HH:mm:ss, (without AM/PM) it is not mapping correctly (eg. source date -

21/05/2020 05:00:00 , target mapped date - 21-05-2020 01:00:00)

Is the issue because of slashes(/) in the csv file date field? Or is it because of different time zones?Maybe the one's coming as DD/MM/YYYY HH:mm:ss, (without AM/PM) from import can be from different time zone?

I have referred below kb article from HI support

https://hi.service-now.com/kb_view.do?sysparm_article=KB0622865

Anyone faced this issue before , please let me know the solution.

Thanks.

[Allen Andreas]

Hi,

I'm really sorry, but this post is pretty confusing and is sort of all over the place.

The basic issue is...you have a date/time field you are importing in...and trying to transform that to go a date/time field as well, correct?

All you need to do in your transform map is let is know the format of the date/time field coming in. It will convert it to work with the target field.

Please mark reply as Helpful/Correct, if applicable. Thanks!

[Allen Andreas]

Hi,

I'll reply here since you replied below.

As mentioned previously, please set the date/time in your transform map field to exactly how it's listed in the incoming source. Not the target field's date/time, but set the incoming source's date/time field exactly how it's coming in.

Please mark reply as Helpful/Correct, if applicable. Thanks!

[kBahu]

Yes both source and target fields are date/time fields. I did mention the format in transform map but it's not working. 2. time format set for the field on transform map is dd-MM-yyyy HH:mm:ss 4.onBefore script - var sourceDate = source.u_endtime; var gdt1 = new GlideDateTime(); gdt1.setDisplayValue(sourceDate,'dd-MM-yyyy HH:mm:ss'); target.u_ooo_end_date = gdt1.getDisplayValue();

[Ankur Bawiskar]

Hi,

so you have set format as dd-MM-yyyy HH:mm:ss during field map?

Is the incoming value in that format?

Can you share the screenshot of the import set table field data?

Regards
Ankur