Glide Date Time Conversion

[sam352120]

HI All,

Facing an error message like : (Unable to format undefined-undefined-Nov 22, using format string yyyy-MM-dd HH:mm:ss ) while loading data through transform map to the Service Now target table.

Source date time field format: Jan 17, 2019 03:18 PM   /   Target date field format: 25-03-2019 22:50:03 (Glide date time format).

I tried to achieve that through scripting in the field mapping of the transform map,But could not succeed.Let me know your inputs.

Script Use:

answer = (function transformEntry(source) {

// Add your code here
var str=source.u_shipping_date;
var strt1 = strt.split(" ");
//gs.print(strt1[0]);
var g = strt1[0].split("-");
//gs.print(g[0]);
//gs.print(g[1]+"-"+g[2]+"-"+g[0]+" "+strt1[1]);
return g[1]+"-"+g[2]+"-"+g[0]+" "+strt1[1]; // return the value to be put into the target field

})(source);

Thanks,

Sambit

[sam352120]

Hi Sanaa,

Thanks for your input.Will the code work in the field mapping script.Also I need to make it dynamic ,

So that it will be  apart of the daily load.

Suggestions..

Thanks,

Sambit

[The SN Nerd]

As Sanaa has mentioned, I have answered this in my blog Secrets of GlideDateTime .

For your example, the answer is below:

answer = (function transformEntry(source) {

var date = source.u_shipping_date// example 'Jan 17, 2019 03:18 PM';
var simpleDateFormat = 'MMM dd, yyyy hh:mm a';
var gdt = new GlideDateTime();
gdt.setDisplayValue(date,simpleDateFormat);

return gdt.getDisplayValue();
}(source);

Example output

17/01/2019 15:18:00

[sam352120]

Hi Paul,

Thanks for your valuable input.I am able to upload the date field in the target table ,But the date/time data which is loaded to the target table shows a different date and time than that of the source data.It is showing 11 hours ahead of the date/time value provided in the source field.

For EX: Date/Time value in the Source field : Nov 24, 2016 10:14 AM

           Date/Time value populated in the target field : 24-11-2016 21:14:00

Can this be achieved by putting ana extra logic in the transform map script.

Thanks,

Sambit

[The SN Nerd]

Add this line

gdt.addSeconds(-39600); //60 seconds * 60 minutes * 11 hours

So the final code:

answer = (function transformEntry(source) {

var date = source.u_shipping_date; // example 'Jan 17, 2019 03:18 PM';
var simpleDateFormat = 'MMM dd, yyyy hh:mm a';
var gdt = new GlideDateTime();
gdt.setDisplayValue(date,simpleDateFormat);
//Adjust for -11 hours
gdt.addSeconds(-39600); // 60 seconds * 60 minutes * 11 hours

return gdt.getDisplayValue();
}(source);

[sam352120]

Hi Paul,

Thanks a lot for your inputs, but I am having one more situation where I need to transform the date field to the target computer table. It is transforming the date but showing 11 hr ahead as faced before.

Script:

-------

answer = (function transformEntry(source) {

var str = source.u_shipping_date; // example 'May 25, 2016';
var simpleDateFormat = 'MMM dd, yyyy';
var gdt = new GlideDateTime();
gdt.setDisplayValue(str,simpleDateFormat);
var dateField = gdt.getDate().getDisplayValue();
return dateField;

})(source);

Thanks,

Sambit