Unable to format 29/09/2021 using format string yyyy-MM-dd HH:mm:ss for field u_warranty_start_date

[Ankita34]

we are trying to import data using excel sheet but we are reieving below error in contract table.

Unable to format 29/09/2021 using format string yyyy-MM-dd HH:mm:ss for field u_warranty_start_date.

[Saurav11]

Hello,

The above error occurs when the date format of field is different from the data format in which you are passing the value.

So you need to change the format in the excel and pass the value exactly in the same format as that of the field in which the value is to be stored or define the format in the mapping of that field in transform map.

[Richard Hine]

Ankita,

If you are not too bothered about the timestamping on the record, you could do a simple GlideDateTime manipulation in the transform script using setValueUTC or setValue methods to avoid mass updating in the source document. Example below of how this looks in a background script.

var myDate = '29/09/2021';

var gdt = new GlideDateTime();
gdt.setValueUTC(myDate, "dd/MM/yyyy");
gs.info(gdt.getDisplayValue());

Hope this helps,

Richard

[shloke04]

Hi,

Please find the steps below to achieve your requirement:

1) Navigate to your transform map and open the Field Map which you are using to map this to u_warranty_start_date.

2) Then click on the checkbox as "Source Script" and use the script below:

answer = (function transformEntry(source) {

    // Add your code here
    var date = source.FIELDNAME + ""; // to string this mad replace "FIELDNAME " with your source field name
    // the regex matches each of the slashes that are seperated, so its like this
    // [month,day,year]
    // updated with a better regex
    var month = date.match(/([0-9].*)\/([0-9].*)\/([0-9].*)/)[1];
    var day = date.match(/([0-9].*)\/([0-9].*)\/([0-9].*)/)[2];
    var year = date.match(/([0-9].*)\/([0-9].*)\/([0-9].*)/)[3];
    var newDate = year + "-" + month + "-" + day; // You can change this to any format you want by writing may be a month or day or year as you want.
   
    return newDate; // return the value to be put into the target field

})(source);

Hope this helps. Please mark the answer as correct/helpful based on impact.

Regards,
Shloke

[marwel]

We can also use the following code:

answer = (function transformEntry(source) {

    var date = source.FIELDNAME + ""; // in place of FIELDNAME replace the name of your variable
    var parts = date.split("/"); //we are creating new array by splitting date in format 2022/02/15 to [2022, 02, 15]
    var newDate = parts[0] + "-" + parts[1] + "-" + parts[2];
    return (newDate);

})(source);