Query records in a date range — ServiceNow

Server-sideintermediateTested on Xanadu

Chrome Extension

Sourdough: ServiceNow Monitoring and Analytics

A Chrome extension for ServiceNow Admins and Developers with essential tools, analytics, graphs and monitoring features.

Instance HealthGraphs & ChartsAPI HealthDeveloper ToolsQuick SearchInstance Switcher

Add to Chrome

Free to install. Pro $5/month after a 14-day no-card trial.
Pro requires the ServiceNow admin role. Upgrade inside the extension.

Overview

Tasks

CMDB

API

Metrics

Monitor

Internals

Instance:sourdoughdev·Version:Yokohama

Instance StateONLINE

System StatusFully Operational

Session Timeout90 minutes

Logged-In Sessions2 (20 active)

Build Nameyokohama-12-18-2024_p1

IP Address10.159.128.43

Instance HealthHealth Score: 90%

🔥 5dSourdough (Chrome Plugin)Dark Mode

Retrieve records created or updated within a specific date range

Script Include

var IncidentRangeQuery = Class.create();
IncidentRangeQuery.prototype = {

    getIncidentsCreatedBetween: function(startDate, endDate) {
        var incidents = new GlideRecord('incident');
        
        // Convert JS Date objects to GlideDateTime in platform timezone
        var gdt_start = new GlideDateTime();
        gdt_start.setValueUTC(startDate, 'yyyy-MM-dd HH:mm:ss');
        
        var gdt_end = new GlideDateTime();
        gdt_end.setValueUTC(endDate, 'yyyy-MM-dd HH:mm:ss');
        
        incidents.addQuery('sys_created_on', '>=', gdt_start);
        incidents.addQuery('sys_created_on', '<=', gdt_end);
        incidents.addQuery('state', '!=', 7); // Exclude closed
        incidents.orderBy('sys_created_on');
        
        var results = [];
        incidents.query();
        
        // Return empty array if no records found
        if (!incidents.hasNext()) {
            return results;
        }
        
        while (incidents.next()) {
            results.push({
                sys_id: incidents.sys_id.toString(),
                number: incidents.number.toString(),
                created: incidents.sys_created_on.getDisplayValue(),
                priority: incidents.priority.toString()
            });
        }
        
        return results;
    },

    type: 'IncidentRangeQuery'
};

Date range queries fail when developers pass JavaScript Date objects directly to addQuery() or forget that ServiceNow stores all dates in UTC while displaying them in the user's timezone. The platform requires GlideDateTime objects for reliable date comparisons, and you must explicitly handle the UTC conversion or your queries will return records from different date boundaries than expected. Most developers get burned by this when their date range queries work in development but fail in production environments with different timezones.

When to use this

Processing records created or modified within specific date boundaries in Business Rules or Scheduled Jobs
Building custom reporting datasets that need precise date filtering with multiple field access per record
Data cleanup or migration scripts where you need to identify records within a specific timeframe
SLA calculations or escalation logic that depends on record creation timestamps

When NOT to use this

Don't use this when you only need record counts — use GlideAggregate with addAggregate('COUNT') instead
Don't iterate date range queries inside loops — you'll create N+1 query problems that destroy performance at scale
Don't use this in client-side scripts — dates are unreliable across browser timezones, use GlideAjax to call server-side logic
Don't query massive date ranges without setLimit() — you'll hit transaction timeout limits and impact system performance

Key behaviors and gotchas

ServiceNow stores all datetime fields in UTC regardless of user timezone — always use GlideDateTime with explicit UTC conversion
The sys_created_on field is indexed on all tables — use it for the primary date filter to get optimal query performance
Date range queries with >= and <= are inclusive on both ends — use < for exclusive end boundaries
Domain separation affects date range queries — records in different domains won't appear unless you have cross-domain read access
ACL restrictions apply to date range queries — users can only see records they have table-level read access to
Always call toString() on GlideElement values when building return objects — raw GlideElements cause serialization issues

⚠️

JavaScript Date.toISOString() creates dates in UTC, but if you pass that string directly to addQuery(), ServiceNow interprets it in the system timezone. Always use GlideDateTime.setValueUTC() to ensure consistent timezone handling across different ServiceNow instances.

Free Newsletter

Enjoying this? Get one deep-dive per week.

Join 1,000+ ServiceNow pros — scripts, GlideRecord patterns, Flow Designer techniques, and career moves. Free.

Alternative Approaches

GlideAggregate for count-only queries

var ag = new GlideAggregate('incident');
var gdt_start = new GlideDateTime();
gdt_start.setValueUTC('2024-01-01 00:00:00', 'yyyy-MM-dd HH:mm:ss');
var gdt_end = new GlideDateTime();
gdt_end.setValueUTC('2024-01-31 23:59:59', 'yyyy-MM-dd HH:mm:ss');

ag.addQuery('sys_created_on', '>=', gdt_start);
ag.addQuery('sys_created_on', '<=', gdt_end);
ag.addAggregate('COUNT');
ag.query();

if (ag.next()) {
    return parseInt(ag.getAggregate('COUNT'));
}
return 0;

Use this when you only need the count of records in a date range, not the actual record data.

Trade-offs: Much faster execution but provides no access to individual record fields or values.

API Methods Used

GlideRecord.addQuery()GlideRecord.orderBy()GlideAggregate.addAggregate()

Test Your Knowledge

Quick 3-question quiz — see how your ServiceNow skills stack up.

Question 1 of 3Performance

A list view on a table with millions of records is slow. Best fix?

Select an answer to continue