How to get 5 business days old record

[Akash Kadam1]

For taking 5 days old record i tried using the created on field but it will take not exactly 5 days old record its taking 3,4,2,1 days records if its have the records in servicenow.

I have to get those record in schedule script but its not fetching record & tried on background script, This is below script : 

var now = new GlideDateTime();

    now.addDays(-5);

    var startOfDay = new GlideDateTime(now.getYear(), now.getMonthUTC(), now.getDayOfMonthUTC(), 0, 0, 0);

    var endOfDay = new GlideDateTime(now.getYear(), now.getMonthUTC(), now.getDayOfMonthUTC(), 23, 59, 59);

 var findRecords = new GlideRecord('sysapproval_approver'); //GR the Approval Table

    findRecords.addQuery('sys_created_on', '>=', startOfDay);

    findRecords.addQuery('sys_created_on', '<=', endOfDay);

    findRecords.addQuery('state', 'requested'); //State is requested

    findRecords.addAggregate('document_id');

    findRecords.query();

    gs.log('findrecord : ' + findRecords.getRowCount());

 while (findRecords.next()) {

gs.print('Five Days old record' +findRecords);

}

[VarunS]

@Akash Kadam1  Try the below script

// Create a GlideDateTime object for the current date and time
var now = new GlideDateTime();

// Subtract 5 days from the current date
now.addDaysUTC(-5);

// Create a GlideDateTime object for the start of the day 5 days ago
var startOfDay = new GlideDateTime(now.getYearUTC(), now.getMonthUTC(), now.getDayOfMonthUTC(), 0, 0, 0);

// Create a GlideDateTime object for the end of the day 5 days ago
var endOfDay = new GlideDateTime(now.getYearUTC(), now.getMonthUTC(), now.getDayOfMonthUTC(), 23, 59, 59);

// Log the start and end of day for debugging
gs.log('Start of Day: ' + startOfDay);
gs.log('End of Day: ' + endOfDay);

// Query the 'sysapproval_approver' table
var findRecords = new GlideRecord('sysapproval_approver');
findRecords.addQuery('sys_created_on', '>=', startOfDay);
findRecords.addQuery('sys_created_on', '<=', endOfDay);
findRecords.addQuery('state', 'requested');
findRecords.query();

// Log the number of records found
gs.log('Number of records found: ' + findRecords.getRowCount());

// Loop through the records and process them
while (findRecords.next()) {
    gs.print('Five Days Old Record: ' + findRecords.sys_id + ' - Created On: ' + findRecords.sys_created_on);
}

[Rampriya-S]

var now = new GlideDateTime();
now.addDays(-5);
var startOfDay = new GlideDate();
var endOfDay = now.getDate();
var findRecords = new GlideRecord('sysapproval_approver'); //GR the Approval Table
findRecords.addQuery('sys_created_on', '>=', startOfDay);
findRecords.addQuery('sys_created_on', '<=', endOfDay);

Can you try this?

[Its_Azar]

Hi there @Akash Kadam1 

you can create a function to calculate the correct date considering only business days. Here's an improved version of your script that ensures you're getting records from exactly 5 business days ago:

function getBusinessDaysAgo(days) {
    var gd = new GlideDateTime();
    while (days > 0) {
        gd.addDays(-1);
        if (gd.getDayOfWeekLocalTime() != 6 && gd.getDayOfWeekLocalTime() != 0) { // Skip Saturday (6) and Sunday (0)
            days--;
        }
    }
    return gd;
}

var fiveBusinessDaysAgo = getBusinessDaysAgo(5);
var startOfDay = new GlideDateTime(fiveBusinessDaysAgo.getYear(), fiveBusinessDaysAgo.getMonthUTC(), fiveBusinessDaysAgo.getDayOfMonthUTC(), 0, 0, 0);
var endOfDay = new GlideDateTime(fiveBusinessDaysAgo.getYear(), fiveBusinessDaysAgo.getMonthUTC(), fiveBusinessDaysAgo.getDayOfMonthUTC(), 23, 59, 59);

var findRecords = new GlideRecord('sysapproval_approver'); // GR the Approval Table
findRecords.addQuery('sys_created_on', '>=', startOfDay);
findRecords.addQuery('sys_created_on', '<=', endOfDay);
findRecords.addQuery('state', 'requested'); // State is requested
findRecords.query();
gs.log('findRecords count: ' + findRecords.getRowCount());

while (findRecords.next()) {
    gs.print('Five Business Days Old Record: ' + findRecords.sys_id);
}

In this script, the getBusinessDaysAgo function calculates the date exactly 5 business days ago by decrementing the date one day at a time and skipping weekends.

If this helps kindly accept the answer thanks much.

[Akash Kadam1]

Thanks while running in background script its giving error like below:

Javascript compiler exception: Java constructor for "com.glide.glideobject.GlideDateTime" with arguments "number,number,number,number,number,number" not found. (null.null.script; line 13) in:
function getBusinessDaysAgo(days) {
var gd = new GlideDateTime();
while (days > 0) {
gd.addDays(-1);
if (gd.getDayOfWeekLocalTime() != 6 && gd.getDayOfWeekLocalTime() != 0) { // Skip Saturday (6) and Sunday (0)
days--;
}
}
return gd;
}

var fiveBusinessDaysAgo = getBusinessDaysAgo(5);
var startOfDay = new GlideDateTime(fiveBusinessDaysAgo.getYear(), fiveBusinessDaysAgo.getMonthUTC(), fiveBusinessDaysAgo.getDayOfMonthUTC(), 0, 0, 0);
var endOfDa...