Convert UCT Date/Time String to MDT

[ljschwaberow]

I am creating an inbound email action to create an incident record. There is a string value on the email that comes across in the email in this format: receivedTime:Aug 09 2016 16:56:17.713 UTC. The requirements state to have this field map to a customized field we have called 'First Observed', which is a date and time field. I have the following script in the inbound email action that maps the date/time string to this field exactly as it comes across:

//Copy Received Time from email to First Observed field on the Incident

var rTime = email.body.receivedtime;

var pTime = rTime.slice(0,20);

var sec = Date.parse(pTime);

var gdt = new GlideDateTime();

gdt.setNumericValue(sec);

current.u_first_observed = gdt.getDisplayValue();

From you the email you can tell that the time is in UTC and the users need to time to come across in our timezone which is US/Mountain. Can anyone assist me with how to convert a string date/time value to US/Mountain time?

Thanks,

Lindsey

[Shawn Dowler]

This might seem really simple, but have you tried replacing the last line with:

current.u_first_observed = gdt.getValue();

The record wants the UTC time in that field, so it can convert it to the user's time zone on display.

Using getDisplayValue() explicitly converts the date too early in the process, so the value of the field gets a Non-UTC datetime, which makes it misbehave when you view the record.

[ljschwaberow]

Hi Shawn,

I tried this and it's converting the time 7 hours ahead instead of 7 hours back.

[Geoffrey2 · Accepted solution]

ServiceNow stores Date/Time values in UTC in the database.   When a Date/Time field is displayed on a form, the value is automatically adjusted to the User's timezone.   So you don't actually have to convert the UTC time to your timezone.   You just need to make sure that the value is saved as UTC in the database.

Here is the code that works for me:

var receivedTime = 'Aug 09 2016 16:56:17.713 UTC';
var dateStr = receivedTime.substring(0,receivedTime.indexOf('.'));
var gdt = new GlideDateTime();
gdt.setValueUTC(dateStr, "MMM dd yyyy HH:mm:ss");

gdt.add(gdt.getTZOffset());

current.u_first_observed = gdt.getValue();

The problem with your Date.parse() function is that it doesn't like the milliseconds in the string.   So sec is actually being set to NaN every time. You can remove the milliseconds value, but I just used gdt.setValueUTC('dateTime', 'format') instead.

gdt.getValue() returns the database format of gdt. If you enter a log statement at Line 5, you will see the date/time value you want of '2016-08-09 16:56:17'.

However, I found that when you try and save that value into your field, ServiceNow tries to adjust to your timezone and inserts the incorrect value.   To compensate, I added the timezone offset of the current User on Line 6.

Now the field is updated with the desired value in UTC.   And when you view the form, the display value will be automatically adjusted to your timezone.

[ljschwaberow]

I posted initially that this wasn't working...but quickly deleted it because it was a typo on my part. This works great, thank you!