How to compare two date fields in url query?

[jaysumara99]

Hello List,

I have this weird problem, and need some advice.

in incident table I created a custom field "u_eassynchedat" as a date. I update it with "javascript:gs.nowNoTZ()", which sets it with current timestamp and at the same time it updates sys_updated_on with "visibly" same timestamp. When I see the incident list, I see exactly same time. So far good.

Now when I run a query to retrieve records with u_eassynchedat less than sys_updated_on, it gives me all records even when they show both values same. Here is the query I am running:

https://snowd.com/api/now/table/incident?sysparm_query=u_eassynchedat<sys_updated_on&sysparm_fields=...

And here is what I get:

-<response>

        <result>

                            <sys_updated_on>2016-04-08 20:34:53</sys_updated_on>

                            <u_eassynchedat>2016-04-08 20:34:53</u_eassynchedat>

        </result>

        <result>

                            <sys_updated_on>2016-04-08 20:34:59</sys_updated_on>

                            <u_eassynchedat>2016-04-08 20:34:59</u_eassynchedat>

        </result>

</response>

When I use absolute value in place of sys_updated_on (such as "u_eassynchedat<2016-04-08 20:34:55") it works, but for some reason it fails when I compare it with sys_updated_on. I also tried "u_eassynchedatLESSTHANsys_updated_on", but no use.

So what am I doing wrong?

Regards,

Jay

[Manjul Katare]

Try if this help!

Comparing two date fields or Scripting in Business Rules - ServiceNow Wiki   (look for : 3 Comparing Two Date Fields)

-Manjul

[Ken_Michelson · Accepted solution]

One of the tricks I use quite often is to build the query I need in List View and the use "copy query" to see the resulting encoded query. This often provides you what you need for URLs or glide record queries (sample below). Doing such I see that this is the correct URL logic: u_eassynchedatMORETHANsys_updated_on@day@before@0   [read it as "more than 0 days before sys_updated_on"]

[jaysumara99]

I understood that instead of "morethan" I was doing "lessthan". Changing that part worked. Thanks for taking time to show the whole thing.

Regards,

Jay

[Ken_Michelson]

not quite. "Less Than" just doesn't work for dates in this scenario even though it logically makes sense. "More than 0 days before" does work. Just one of those quirks you get used to.