Using Easy Import feature to import date column produces unexpected results

 

Issue

---

When using easy import and an import template to import data with a date column, dates are not being converted correctly in import set and target table.

 

Symptoms

---

When using easy import and an import template from table, for example alm_hardware, dates are not being converted correctly. The OOB easy import template, without modifying the import set or transform map, verified the date had been entered correctly in the Excel document. The date looks correct in the import set, but during the transform it is not being updated correctly.

• Date in Excel displays 2020-10-10, entered as 10/10/2020, correctly
• After Import runs, clicking preview data button shows record with Warranty Expiration Date as 10-10-2020
• Choosing Complete Data Import transforms the data with a Warranty Expiration Date as 04-11-0016 

Steps to Reproduce:

1. Set logged in user record Date format column to MM-dd-yyyy.
   
    
2. From Navigation Bar, type alm_hardware.LIST, hit Enter.
3. Right click on a column and select Import.
4. Choose the following values on the Easy Import screen and click Create Excel Template as illustrated below:
   
   
5. Click the Download button.
6. Change Warranty Expiration Date on Excel Spreadsheet. Enter 10/20/2020. Excel auto formats the date to 2010-10-10. Save Excel File
7. Navigate back to alm_hardware.LIST. Right click a column header and choose Import.
8. When completed, choose Preview Data. Notice the date format is in the same format as the logged in user's Date Format, in this case MM-dd-yyyy:
   
   
9. Choose Complete Data Import. Navigate back to alm_hardware.LIST and search for that record. You will notice an incorrect date was saved as 04-11-0016:
   
   

The Warranty Expiration Date value is 04-11-2020. 

  

Cause

---

When User Profile, sys_user.LIST, Date Format is set to an actual format, instead of --None--, the Easy Import process imports the data to the staging table using the date format set in the Date Format field on User Form. In this case, it was set to MM-dd-yyyy.

Note - The Easy Import process auto-created an import set row table with a column u_warranty_expiration with datatype as Date, which also matches the target column Warranty Expiration data type. 

Resolution

---

There are two options to work around this issue:

1. Change the Date Format of the sys_user record, or User Form, to match the date format that the target table column is expecting, in this case MM-dd-yyyy. Or, change it to --None--. Changing to None forces the instance to use the format stored in the glide.sys.date_format system property.
2. Add a system property with following values:
   
   Name: glide.excel.use_user_date_format
   Type: true | false 
   Default value: false 

Information: This property determines whether Excel exports use the date/time format specified in a user's profile is true. If false, exports the instance date/time format defined by the glide.sys.date_format property. 

To accomplish option 1:

1. In Navigation Bar, type sys_user.LIST.
2. From the User List, search the user record performing the import.
3. Change Date Format field to match the date format that the target column is expecting. Or set to --None--
4. Save and retry Easy Import process.

To accomplish option 2, which is a global setting affecting all Easy Imports involving dates:

1. Make sure you are logged in as Admin, go to Navigation Bar, type sys_properties.LIST.
2. Click New and add new property with values as listed:

Name: glide.excel.use_user_date_format
Type: true | false 
Default value: false  

 

References

---

Available Properties Docs: https://docs.servicenow.com/bundle/jakarta-servicenow-platform/page/administer/reference-pages/reference/r_AvailableSystemProperties.html

Note: Creating a System Property is a global setting which will affect all users for a particular process.