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.

[Santosh_Ksagar]

Hi,

You can also use scipt in field mapping instead of onBefore script and set date/time format there as well.

Thanks
Santosh

[kBahu]

Hello Santosh, 

Thanks for the suggestion. Yes i am able to see the transform now without the error ( 'Unable to format 21/05/2020 05:00:00 using format string dd-MM-yyyy HH:mm:ss for field u_ooo_end_date') but still facing issues with incorrect mapping of time. As below.

[Ankur Bawiskar · Accepted solution]

Hi,

please type this format in the field map date format and test once

dd/mm/yyyy HH:mm:ss

screenshot below

Mark ✅ Correct if this solves your issue and also mark 👍 Helpful if you find my response worthy based on the impact.
Thanks
Ankur

[kBahu]

Thank you so much Ankur, it worked. No need of source script and conversion to a date format, just by changing the format to dd/MM/yyyy HH:mm:ss, it really worked. As this format option was not available initially, could not think of changing it. 

Thank you so much for the solution.

[Ankur Bawiskar]

Glad that it helped

Regards
Ankur