accountExpires attribute import to User Table

[Cory Hitchings]

Hi all, 

I've been trying to import the accountExpires attribute from AD into ServiceNow sys_user table and having some issues with the date format. 

I have a new field on the sys_user form called u_end_date in a Date/Time variable with a limit of 100.  

Our AD date format appears like this:  

(Month/Day/Year) (Hour:Minutes:Seconds) (AM/PM) (Timezone)

Looking at this, it leads me to believe the format should be: 

mm/dd/yyyy hh:mm:ss a z

However; when I run the LDAP User import, it errors on each individual with an accountExpires date with this message: Unable to format undefined using format string MM/dd/yyyy hh:mm:ss a z for field u_end_date 

Here is my script for accountExpires Field Map: 

answer = (function transformEntry(source) {
	// This function converts the UTC date/time to local date/time
function getLocalTimeZoneDateTime(dateTimeUTC) {
  var gdt = new GlideDateTime(dateTimeUTC);
  return gdt.getDisplayValueInternal();
}

var dtUtil = new DateTimeUtils();

if (source.u_accountexpires === undefined) {
      // set a blank value if the source is 'undefined'
      target.setValue(u_end_date,'');
} else if (source.u_accountexpires != 0) {
      // convert the date from int8 format to GlideDateTime in UTC
      var expiresUTC = dtUtil.int8ToGlideDateTime(source.u_accountexpires);
      // convert the GlideDateTime in UTC to Local TimeZone
      var expiresLocal = getLocalTimeZoneDateTime(expiresUTC.toString());
      //log.info("ExpiresLocal: " + expiresLocal);
      // Set the value to the Local Time Zone value
      answer = expiresLocal;
} else {
      // set a blank value if the source is anything else.
      target.setValue("u_end_date",'');
}

// 	return ""; // return the value to be put into the target field

})(source);

I've tried the following but out success with any of these variations:

MM/dd/yyyy hh:mm:ss
yyyy/dd/MM hh:mm:ss

MM/dd/yyyy hh:mm:ss a
yyyy/dd/MM hh:mm:ss a

MM/dd/yyyy hh:mm:ss z
yyyy/dd/MM hh:mm:ss z

MM/dd/yyyy hh:mm:ss a z
yyyy/dd/MM hh:mm:ss a z

Any ideas what date format this should be configured as?  Thanks so much in advance! 

[Cory Hitchings]

Well Mike, that was it 100%.  Thank you so very much for your help and your patience with this one.  Truly appreciate your diligence!  Merry Christmas and have a Happy healthy New Year!  Take good care!

[Ankur Bawiskar]

@Cory Hitchings 

Can you use this in your onBefore transform script

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {

	// Add your code here

var incomingValue = source.u_accountexpires;

var gdt = new GlideDateTime();

gdt.setDisplayValue(str, "MM/dd/yyyy hh:mm:ss a z");

target.u_end_date = gdt.getValue();

})(source, map, log, target);

Regards
Ankur

[Ankur Bawiskar]

@Cory Hitchings 

The value in the import set table doesn't seem to be of that format

MM/dd/yyyy hh:mm:ss a z

Regards
Ankur

[Cory Hitchings]

Hi Ankur, 

Thank you, I have tried your code for the onBefore transform script using both "MM/dd/yyyy hh:mm:ss a z" and "MM/dd/yyyy hh:mm:ss" with no success.   All fields are still showing (empty) after the import.  

[rajneeshbaranwa]

Can you try using below ?

var incomingValue = source.u_accountexpires;
incomingValue = incomingValue.toString().subString(0,22);

var gdt = new GlideDateTime();

gdt.setDisplayValue(incomingValue, "MM/dd/yyyy hh:mm:ss a");

target.u_end_date = gdt.getValue();