Working with Dates in Inbound Email Actions

[jmiskey]

We are forwarding emails to ServiceNow and then programming an Inbound Email Action to update the User record based on the contents of the email.  Working with dates and date comparison has always been a little tricky in ServiceNow/Java, especially when you have to convert them from text to date and then do a comparison on them.

In the body of the email, I will have a line of text that will look like this:

Termination Date 12/07/2023

It will always be the phrase "Termination Date" followed by a date in "mm/dd/yyyy" format.

In the Script for this Inbound Email Action, I need to compare this date to see if it is a future date (after today).  I already figured out how to extract the date part of the string, like this:

//get values from email body
var ebody = email.body_text;

//get termination date string
var temp2 = ebody;
var b = temp2.indexOf("Termination Date");
temp2 = temp2.substr(b + 16, b + 30);
temp2 = temp2.trim();
temp2 = temp2.substr(0, 10);

so that temp2 returns "12/07/2023".

But now I need to compare this date to the current date and determine whether or not it is a future date.

Can someone help with that part of the script?

Thanks

[Ethan Davies · Accepted solution]

So you're going to need to convert the date you have in the text to a GlideDateTime object in ServiceNow. Once you do that, you can compare it with the current date. First, let's create a GlideDateTime object for your termination date.

var terminationDate = new GlideDateTime('12/04/2023');

Next, we want to create a comparison to today's date (or any other date you may like).

var currentDate = new GlideDateTime();

Now that we have two date objects, we can compare them both using the compareTo() method.

var terminationDate = new GlideDateTime('12/04/2023');
var currentDate = new GlideDateTime();

var compareDates = terminationDate.compareTo(currentDate);

if (compareDates == -1) {
    // The termination date has passed, process your code here.
} else {
    // The termination date has not passed, process your code here.
}

[jmiskey]

OK, it hasn't quite worked out.  Obviously, I cannot use a hard-coded date in the terminationDate variable declaration.  So I substituted the hard-coded date you had in there with my variable.

This is what my code looks like:

//get values from email body
var ebody = email.body_text;

//find employee number
var temp = ebody;
var a = temp.indexOf("Employee Number:");
temp = temp.substr(a + 16, a + 26);
temp = temp.trim();
var ee_num = temp.substr(0, 7);
gs.log("JOE1 EE Num: " + ee_num);

//find termination date (string)
var temp2 = ebody;
var b = temp2.indexOf("Termination Date");
temp2 = temp2.substr(b + 16, b + 30);
temp2 = temp2.trim();
temp2 = temp2.substr(0, 10);
gs.log("JOE2 Term Date String: " + temp2);

//convert to valid date and compare to current date
var terminationDate = new GlideDate(temp2); 
gs.log("JOE3 Term Date Date: " + terminationDate);
var currentDate = new GlideDateTime(); 

//compare dates
var compareDates = terminationDate.compareTo(currentDate);
gs.log("JOE3 compareDates: " + compareDates);

if (compareDates == -1) {
	var usr = new GlideRecord('sys_user');
	usr.addQuery('employee_number', ee_num);
	usr.query();

	while (usr.next()) {
		gs.log("JOE4: LOCK TERM USER ACCOUNT: " + usr.employee_number);
		usr.locked_out = 'true';
		usr.update();
	}
}

As you can see, I added various log values along the way to verify what the value is at each point along the way and to verify that it got that far.  Here is the last value it logged:

JOE2 Term Date String: 12/05/2023

You can see that is right before it tries to set the value of the terminationDate field.  So that is where the code is failing.  It does not seem to like this line of code:

var terminationDate = new GlideDate(temp2); 

[Ethan Davies]

@jmiskey use GlideDateTime instead of GlideDate. GlideDate was a typo on my part in the original response, I have updated it.

[jmiskey]

Thanks for catching my typo!  That was the issue!