Date is not pulling properly with the correct format

[Poorva Bhawsar]

Hi Community,

We are pulling data from snowflake to servicenow. While pulling the data we are facing issue with the date format.

In source data, we have the below format:

In servicenow response body, we are getting below response:

We have a scheduled job for this. Here is the logic for the dates:

gr.assignment_start_date = outputJSON.data[i].start_date.toString();

One more issue we have is when we are pulling data into the response body we are not able to pull 2 other fields data which is start date and end dates.

Please help us with that how we can get the start and end dates in proper format.

Thanks

[Abhay Kumar1]

@Poorva Bhawsar Instead of using toString(), you should parse the date properly and format it as expected by ServiceNow. Here’s how you can handle this in your script:

// Parsing the start_date and end_date from the response

var startDate = new GlideDateTime(outputJSON.data[i].start_date);

var formattedStartDate = startDate.getValue(); // Returns date in 'yyyy-MM-dd HH:mm:ss' format

var endDate = new GlideDateTime(outputJSON.data[i].end_date);

var formattedEndDate = endDate.getValue(); // Returns date in 'yyyy-MM-dd HH:mm:ss' format

// Assigning the formatted dates to the record

gr.assignment_start_date = formattedStartDate;

gr.assignment_end_date = formattedEndDate;

GlideDateTime can handle various date formats and will convert them to ServiceNow’s expected format (yyyy-MM-dd HH:mm:ss) and getValue() retrieves the date in the correct format for assignment.

Hope this will help you.

[AnveshKumar M · Accepted solution]

Hello @Poorva Bhawsar ,

SNow flake stores the date values in Number format. You can use TO_DATE with the date fields in the SQL query that is being used to convert the data in to proper data format. Otherwise you can use the following script in your scheduled job to convert those numbers to date string.

gr.assignment_start_date = formatDate(Number(outputJSON.data[i].start_date));

function formatDate(d) {    
    var milli_seconds = 24 * 60 * 60 * 1000;
    var parsed = d * milli_seconds;
    var date = new Date(parsed);
    date.setTime(date.getTime() + (8*60*60*1000)); //Here 8 is hours - the offset from PST to GMT
   
    month = '' + (date.getMonth() + 1),
    day = '' + date.getDate(),
    year = date.getFullYear();
 
    if (month.length < 2)
        month = '0' + month;
    if (day.length < 2)
        day = '0' + day;
 
    return [year, month, day].join('-');
}

You can call this function anywhere in your script to convert SQL Number dates to Date string.

Please mark my answer helpful 👍 and accept as a solution ✔️ if it helped.

[SumanthBabu]

Thank you... it is working