Help with date input in inbound script to create a CR

[Mollie V]

Hello Dev community,

I'm developing an inbound action to create a CR from an email. But I'm having trouble with the planned start and end dates.

The email is in html format but I'm using email.body_text to get the values I need as input to the CR record.  I can extract the planned start and end dates with no problem but when I assign them to the CR planned start and end, they are not the right values.

Here is part of the script that I need your help with. The input to GlideDateTime() is exactly how the datetime appears in the email. But when I ran this script, the getDisplayValue() returned is not what I expected.

var gd = new GlideDateTime("7/11/2023 6:30 PM");

gs.print('getDisplayValue ' + gd.getDisplayValue());
gs.print('toString ' + gd.toString());
gs.print('getValue ' + gd.getValue());
gs.print('getDisplayValueInternal ' + gd.getDisplayValueInternal());

gs.print(gd.isValid()); //true
gs.print(gd.getErrorMsg()); //reason

*** Script: getDisplayValue 10-Jul-2023 19:00:00
*** Script: toString 2023-07-11 00:00:00
*** Script: getValue 2023-07-11 00:00:00
*** Script: getDisplayValueInternal 2023-07-10 19:00:00
*** Script: true
*** Script: null 

 Does anyone have any suggestions on what I'm missing? 

Thanks in advance for the help!

[Mollie V · Accepted solution]

Hi all, thanks again for the ideas. After trying out various methods, I got it working. Here's the final script in case you're interested and hopefully it helps others.

var planStart = "07/11/2023 9:30 PM";

var dStart = planStart.trim().split(" ");

gs.info(dStart[0]);

gs.info(dStart[0].replaceAll("/","-"));

var tStart = dStart[1]+' '+ dStart[2];

var newDt = dStart[0].replaceAll("/","-")

var newDt2 = newDt.trim().split("-");

var newDt3 = newDt2[0]+ '-' +newDt2[1]+'-' +newDt2[2];

var refDt = newDt3 + ' ' + tStart; 

gs.info ('Plan Start reformatted ' + refDt);

var gd = new GlideDateTime();

gd.setValue(refDt);

gs.print('getValue: '+ gd.getValue());

//this is where we check if AM or PM

var pstr = planStart.toString();

var adjustedTime = gd.getValue();

if (pstr.indexOf('PM') > 0) {

  gd.addSeconds(43200); // add 12 hours

  gs.print('getValue after adding seconds: '+ gd.getValue());

  adjustedTime = gd.getValue();

}

  gs.print('adjusted time: '+ adjustedTime);

[Tushar · Accepted solution]

Hi @Mollie V 

This should help -

var planStart = "07/11/2023 9:30 PM";

// Split date and time
var dStart = planStart.trim().split(" ");
var datePart = dStart[0].replaceAll("/", "-");
var timePart = dStart[1] + " " + dStart[2];

// Combine date and time in the right format
var refDt = datePart + " " + timePart;

// Create a new GlideDateTime object and set its value
var gd = new GlideDateTime();
gd.setValue(refDt);

// If PM, add 12 hours (43200 seconds)
if (planStart.indexOf('PM') > 0) {
  gd.addSeconds(43200); // 12 hours in seconds
}

// Display the final adjusted time
gs.info('Adjusted time: ' + gd.getDisplayValue());

Please, don't forget to mark my answer as correct if it solves your issue or mark it as helpful if it is relevant for you!

Regards,
Tushar

[OlaN]

Hi,

The system does not recognize the format you are sending in as parameter to create the GlideDateTime.

So, I assume, it takes the part it recognizes, the date part, and discards the time part.

You can read more on the Developer site for reference on how you should send in values for date/time values.

[Chetan Mahajan]

Hello @Mollie V ,

                                  It might be happening because of time zone difference. The email body contains the date and time in a specific format, and when you create a new GlideDateTime object, it assumes a default time zone (usually UTC) unless explicitly specified.

Not sure but you can try below time conversion 

// Assume emailTime is the extracted time from the email, e.g., "7/11/2023 6:30 PM"
var emailTime = "7/11/2023 6:30 PM";
var emailTimeZone = "America/New_York"; // Replace with the actual time zone

// Create a new GlideDateTime object using the server's time zone
var serverTime = new GlideDateTime();

// Parse the email time using the email's time zone
var parsedEmailTime = new GlideDateTime(emailTime);
parsedEmailTime.setTZ(emailTimeZone);

// Convert the parsed time to the server's time zone
serverTime.setDisplayValue(parsedEmailTime.getDisplayValue());

// Output the values
gs.print('Parsed Email Time: ' + parsedEmailTime.getDisplayValue());
gs.print('Server Time: ' + serverTime.getDisplayValue());

Kindly mark correct and helpful if applicable

[Mollie V]

Thank you, Chetan, for your response. However, I'm still getting an incorrect date. Btw, I was getting an error on the method setTZ() stating it can't find that method. Anyway, I found another post using setTimeZone("US/Eastern"), and although I'm not getting an error, it's still returning an incorrect date.

It does make sense this is related to timezone, but haven't found anything that is working that returns the correct date with an input date format "7/11/2023 6:30PM".

[thomaskennedy]

I think it's having trouble with the source value's format: it's not supported. Go back to the source and get the data sent in 2023-07-11 18:30 format . For example when I do this, only the third one is parsed as expected. The first two come up with something, but not what you wanted.

var formats = [
  "7/11/2023 6:30 PM", // not supported
  "7.11.2023 06.30",   // supported? Not sure
  "2023-07-11 18:30"   // supported
];
formats.forEach( function(e) {
  var gdt = new GlideDateTime(e);
  gs.info(gdt.getDisplayValue() + " " + gdt.isValid() + " " + gdt.getTZOffset());
} )

2023-07-10 19:00:00 true -18000000
2023-11-06 18:00:00 true -21600000
2023-07-11 13:30:00 true -18000000

Or you could pick it apart yourself and try to reassemble it in that format - a nasty job.