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!
I use below to onBefore transform script to import that from AD. (Make sure to remove field mapping)
// Updates the SN Account Expires field
var n = source.u_accountexpires;
var s = n.toString();
// Only convert AD accountExpires values that begin with 1 (ex. 0 represents never expires)
if (s.charAt(0) == 1) {
var dtUtil = new DateTimeUtils();
var gDate = dtUtil.int8ToGlideDateTime(n);
gDate.addDaysLocalTime(-1);
target.u_account_expires = gDate;
}else if(!target.u_account_expires.nil()) {
target.u_account_expires = '';
} 
The import does complete successfully no errors relating to the u_accountecpires / u_end_date field.
Here is a screen shot of the Tranform Maps:
and a screen capture of the transform scripts:
