Transform Maps - handling CSV date values

[Jordan Hladish1]

It seems, through my quick Google searching, that there isn't a great article in the ServiceNow Community regarding how to handle "non-standard" dates when importing with a Transform Map.

For example - I'm importing a slew of records for a client via a CSV URL export. If you've ever done these before, you may have noticed how the columns are exported as their labels within PDFs or XLS, but as the column value within CSV. This is easy to work around, but date values also come over in a very unfriendly way.

For a date of August 05, 2020 - an XLS export would list this as 08-05-2020 or some similar variation based on your preferences. Within a CSV export, this same date value would be listed as 8-5-2020. This becomes an issue quickly when you need to state a date format for your incoming data as a standard MM-DD-YYYY format with 2 digits at each month/day location.

So what's the quickest/easiest way to handle this? I personally made a Source Script for date values which will return a GlideDateTime value formatted in a way the instance actually likes, and can insert.

Please reuse this, it's extremely helpful to have around. Don't re-teach yourself GDT or try to re-figure out how you did that the last time you saw this again.

// code to standardize incoming date values from CSV exports
    var source_date = source.u_warranty_expiration;
    source_date = source_date.split('/');
    
    var date = new GlideDateTime();
    date.setMonth(source_date[0]);
    date.setDayOfMonth(source_date[1]);
    date.setYear(source_date[2]);
    return date.getDate();

Jordan 'radish' Hladish