GlideRecord query against glide_date_time field

[brian-rowland]

I need to run a query to retrieve records created within a certain date range. Been here: http://wiki.service-now.com/index.php?title=GlideRecord which implies that where operators can be passed in, but the API is short on detail.
Something like:

gr = new GlideRecord('foo');
gr.addQuery("sys_created_on", ">", "2010-01-05");
gr.query();

gets me in the neighborhood, returning everything created on or after 2010-01-05 (though I cannot imagine using string comparison is ideal here), but adding a second addQuery with the end date does not return the expected set. I doubt this is even the best way to interact with glide_date_time fileds from GlideRecord. Any help is appreciated.

Brian

[CapaJC]

For date/time fields, you can add the time. e.g.,
gr.addQuery("sys_created_on", ">=", "2010-01-05 00:00:00");
gr.addQuery("sys_created_on", "<=", "2010-01-05 23:59:59");

The query to the database won't be a string comparison, but this will generate the correct SQL statement to get the right records.

Note that date/time values are stored in the database in UTC timezone (essentially GMT), so this may or may not affect what you need to query on. Testing will let you know.

[brian-rowland]

Thanks for the info - I'll give this a try.

[anezka_neckarov]

I had issue with more complex query with dates, so my solution maybe help somebody

Fields starts and ends are DateTime type

var start = current.getValue('starts');
var end = current.getValue('ends');

gr.addEncodedQuery("startsBETWEEN" + start + "@" + end + "^ORendsBETWEEN" + start + "@" + end + "^NQstarts<" + start + "^ends>" + end);