unable to chnage import set field type decimal to date

[Kanna12]

Error:Unable to format 87,022,700 using format string yyyy-MM-dd HH:mm:ss for field u_last_update_login

we are getting bulk of above errors in scheduled import sets. We are getting data from data source mid server (JDBC). This import set will ran every day. Date coulmn in source table not transformaing as the in source table login_date field type set as Decimal and Target Date field set as Date/Time.

I am trying to change the source table date field type to Date, But system not showing up date option in data field lookup tabel.

Please advice on this.

[Tony Chatfield1]

Hi, you could use a field map script to calculate a new source value that the platform understands,

or use a before transform script to derive correct format\value for your target field.

Can you set the field as a string?

[Kanna12]

Thank you,

Source table set as: Decimal.
Target field set as: Date.

[Amit Gujarathi · Accepted solution]

HI @Kanna12 ,
I trust you are doing great.
Here's a sample script you might use in your Transform Map script:

(function runTransformScript(source, map, log, target /*undefined onStart*/, action /*undefined onStart*/) {
    
    // Assuming the source value is a Unix timestamp in milliseconds
    var decimalTimestamp = source.u_last_update_login;

    // Convert the decimal timestamp to a JavaScript date object
    var date = new Date(decimalTimestamp);

    // Format the date to the ServiceNow acceptable format
    target.u_last_update_login = formatDate(date);

    function formatDate(date) {
        var yyyy = date.getFullYear().toString();
        var MM = pad(date.getMonth() + 1,2);
        var dd = pad(date.getDate(),2);
        var hh = pad(date.getHours(),2);
        var mm = pad(date.getMinutes(),2);
        var ss = pad(date.getSeconds(),2);

        return yyyy + '-' + MM + '-' + dd + ' ' + hh + ':' + mm + ':' + ss;
    }

    function pad(number, length) {
        var str = '' + number;
        while (str.length < length) {
            str = '0' + str;
        }
        return str;
    }

})(source, map, log, target);

[Kanna12]

Thank you, Amit. I will try.