Date time error while importing

[Pavan S]

Hi.

Iam using transform map for importing the holidays list (from excel) into ServiceNow.

But while importing the start date timing is getting changed to 18:30:00 instead of 00:00:00.

The start date time iam importing from excel 

The date it is updating in ServiceNow 

i have tried the onBefore and onAfter scripts but still it didn't work. Also tried to change my Time zone or excel time zone but still it didn't work.

Can anyone help me about how to update the exact time mentioned in the excel sheet. (i have done the same thing from past 3 years bit it worked. Now only facing the issue)

[Ankur Bawiskar]

@Pavan S 

try this in onBefore transform script and try to set display value

target.setDisplayValue('start_date_time', source.u_start_date_time);

💡 If my response helped, please mark it as correct ✅ and close the thread 🔒— this helps future readers find the solution faster! 🙏

[Pavan S]

Hi Ankur, 

I have tried the above on Before script already, but it didn't work😐

[Ankur Bawiskar]

@Pavan S 

excel data is in GMT or which timezone?

what's your timezone when seeing the records?

Did you check in XML of that record?

If you are getting GMT and set GMT then it should be fine

target.setValue('start_date_time', source.u_start_date_time);

💡 If my response helped, please mark it as correct ✅ and close the thread 🔒— this helps future readers find the solution faster! 🙏

[Ankur Bawiskar]

@Pavan S 

I tested and it worked fine for me

I considered the date/time in Excel is in GMT and used the script I shared and it showed the correct value when I selected timezone as GMT in Preferences

In staging table the date came as string

In target it was populated exact

Note: you should always know the date/time in excel/csv is in which timezone, so that you can handle that in your transform script, always set value in GMT

if you got to know the date/time in excel is in Sydney then convert that to GMT and use setValue() to set value in GMT

💡 If my response helped, please mark it as correct ✅ and close the thread 🔒— this helps future readers find the solution faster! 🙏