Format of Date field in transform map

[Alon Grod]

Hi,

In my xlsx file the Date filed is written: 04-AUG-2022

Is there any way to change the date format in the transform map so it will know how to get this format from the xlsx file?

 

[Pavankumar_1]

Hi @Alon Grod ,

we don't have OOTB date format any like 04-AUG-2022. 

1. You can change the date format in excel as ServiceNow date format

or 

2. You need to set the correct format based on few conditions. use below script and add different conditions

var date='04-AUG-2022';
var splitdate = date.split('-')[1].toString();
if(splitdate=='AUG'){// need to give conditions for other months and replace with correct month value
var newDate = date.replace('AUG', '08');
gs.info(newDate);
}

Background script results: dd-mm-yyyy and make sure your target is on same date format

[Alon Grod]

@SoniaShridhar13  i dont want to convert the date, i want to get the date in this format: 04-AUG-2022, but when the transform map run on this field (Date), it does not get the record inside the field, because it does not read this format. how can i read and get the date in this format: 04-AUG-2022

[Pavankumar_1 · Accepted solution]

Hi @Alon Grod ,

No we don't have that  date format in ServiceNow. ServiceNow date fields will not take if you pass 04-AUG-2022 like this.

[jaheerhattiwale]

@Alon Grod Please try below code in field map script:

var targetDate = source.u_date.toString();

var months = ["JAN", "FEB", "MAR", "APR", "MAY", "JUN", "JUL", "AUG", "SEP", "OCT", "NOV", "DEC"];

loop: for(var i=0; i<months.length; i++){

 if(targetDate.indexOf(months[i]) >= 0){

   targetDate = targetDate.replace(months[i], i+1);

   break loop;

 }

}

return targetDate;

Please mark as correct answer if this solves your issue.