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]

Thanks for the post, but that didn't correct the issue.  

[Ankur Bawiskar]

Hi,

Is the value in the import set long int?

Regards
Ankur

[Cory Hitchings]

Hi again Ankur, 

The value from AD is set to long integer, and looks like it is copying the string from AD in the import set...   is this what you mean? 

Thank you! 

[Alex_3]

Hello @Ankur Bawiskar 

I am facing the issue on this,

My Requirement is to Map accountExpires field with u_accountexpires in sys_user table which is in T/F type.

accountExpires = contains a number in C# that represents an expiration date/time, would likely need to be converted to Date/Time format. 
number as, tics since 1601-01-02

any idea what should be done to achieve this

Thanks 

[Deepak Kumavat1]

i fixed it for me as Datetime utils did not convert in Datetime format

var dtUtil = new DateTimeUtils();
    var gdt = new GlideDateTime();
    var simpleDateFormat = 'yyyy-MM-dd HH:mm:ss';
    gdt.setNumericValue(dtUtil.int8ToGlideDateTime(source.u_msds_userpas_rytimecomputed,simpleDateFormat));
    var dateVal = gdt.getDisplayValue();
    target.u_password_expirytime = dateVal;