How to get value from date and time fields using GlideDateTime in fix script

[Spike236]

Hello all,

im working on a fix script to go through active records in a custom table and get the value of the two date and time fields, substract them and update the value for the Time for hire field. Can you help me with locating the issue?

var gr = new GlideRecord("custom table");
gr .addEncodedQuery('active=true);
gr .query();

while (gr .next()) {
    if (!gr .contacted.nil() && !gr .offer_accepted.nil()) {
       var startDate = new GlideDateTime(getValue('contacted'));
        var endDate = new GlideDateTime(getValue('offer_accepted'));
        var duration = GlideDateTime.subtract(startDate, endDate);

        gr .setValue('time_to_hire', duration);
        gr .setWorkflow(false);
        gr .autoSysFields(false);
        gr .update();
    }
}

[Allen Andreas]

Hello,

I would recommend cleaning up the script slightly and joining your script like:

gr.addEncodedQuery('active=true');

Instead of having it as gr. addEncodedQuery with a space in there. Fix that everywhere in your script. Also take note of the bold part of the line I just wrote, you're missing an apostrophe at the end of that.

For retrieving the duration, if that's the type of field you are setting, then you could use something like this:

var startDate = gr.contacted.getGlideObject();  
var endDate = gr.offer_accepted.getGlideObject();  
gr.time_to_hire = gs.dateDiff(startDate.getDisplayValueInternal(),endDate.getDisplayValueInternal(),false);

Just an example, but review what I've done and consider going from there.

Please mark reply as Helpful/Correct, if applicable. Thanks!

[ricker]

Hi Pavel,

You can't have a space after your GlideRecord name and gr is not a recommended variable name.  You were not getting the time value correctly when making your GDT objects.  You were pretty close...I made some changes to your script below, you'll need to set the table name and the field names (contacted, offer_accepted, time_to_hire) need to be right.

Hopefully this helps.

var grTable = new GlideRecord("custom_table");//using gr as the gliderecord variable name is not a good practice and there can't be a space in the table name
grTable.addEncodedQuery('active=true');//put the offer accepted and contacted query in here.
grTable.query();

while (grTable.next()) {
    //if (!gr.contacted.nil() && !gr.offer_accepted.nil()) {//this should be part of the query
       var startDate = new GlideDateTime(grTable.getDisplayValue('contacted'));
        var endDate = new GlideDateTime(grTable.getDisplayValue('offer_accepted'));
        var duration = GlideDateTime.subtract(startDate, endDate);
//gs.info(duration.getDisplayValue());// 27 Days 12 Hours 25 Minutes
        grTable.setValue('time_to_hire', duration);//if this is a duration type field it might have special requirements for setting the value....if it's a string then use getDisplayValue()
        grTable.setWorkflow(false);
        grTable.autoSysFields(false);
        grTable.update();
}

[-O-]

The GlideDateTime constructor expects a date in UTC TZ and in the system's (internal) format.
Getting the display value of a date/time field returns it in the current user's TZ and the current user's date/time format.
So how's

new GlideDateTime(grTable.getDisplayValue('offer_accepted'));

gonna work?

[jaheerhattiwale]

@Spike236 Use this below updated code.

var gr = new GlideRecord("custom table");

gr.addEncodedQuery('active=true);

gr.addQuery("contactedISNOTEMPTY");

gr.addQuery("offer_acceptedISNOTEMPTY");

gr.query();

while (gr.next()) {

       var startDate = new GlideDateTime(gr.getValue('contacted'));

        var endDate = new GlideDateTime(gr.getValue('offer_accepted'));

        var duration = GlideDateTime.subtract(startDate, endDate);

        gr.setValue('time_to_hire', duration.getDisplayValue());

        gr.setWorkflow(false);

        gr.autoSysFields(false);

        gr.update();

}

Please mark as correct answer if this solves your issue.