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.

[Dolly M]

h

[kBahu]

I am using below source script. Will this convert source field date which is actually coming as a 'string' to date/time value?

answer = (function transformEntry(source) {

var gdt = new GlideDateTime();
gdt.setDisplayValue(source,'dd-MM-yyyy HH:mm:ss');

return "gdt.getDisplayValue()";

})(source);

Because its still not giving correct output.

[Santosh_Ksagar]

Hi,

The best way to find out what value is being sent is try to log the values for few test records. 

https://docs.servicenow.com/bundle/orlando-platform-administration/page/administer/import-sets/refer...

https://docs.servicenow.com/bundle/orlando-platform-administration/page/script/server-scripting/refe...

Thanks
Santosh

[shraddha10]

Go to onafter TRANSFORM SCRIPT and use this over there. it will automatically capture the excel value to the field.

This script worked for me. Mark Helpful if this works for you.

run gliderecord on the target table. gr is the object of the gliderecord.

var dateTime = source.field_name;
var gdt = new GlideDateTime(dateTime);
gdt.setDisplayValue(dateTime);
var a = gdt.getValue();
gr.setValue('target_field_name', a);

[Liga]

this Article helped: https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0622865