Relevance help - End Date Days back

I have a query I want to run to get inactive actions “x” days back.

(ID of it, State of it, name of it, end date of it as string | "<null>")
of BES Actions 
whose (
(State of it as lowercase = "expired")
and (Group Member Flag of it = false) 
/*and (End Date of of it < now - 90*day) */
and (End Date of it < ("Tue, 09 Jul 2019" as date))
)

Problem i have is, the “now as date - 20*day” part doesn’t work with “End Date”. This one works:

(ID of it, State of it, name of it, Time Issued of it)
of BES Actions 
whose (
(State of it as lowercase != "open" and State of it as lowercase = "stopped")
and (Group Member Flag of it = false) 
AND (Time Stopped of it < now - 90*day)
)

…but I that is a different query. How you query for expired vs stopped is different. But the “Time Stopped” vs “End Date” part is the thing that is killing me. Why can’t I use “…of it < now - 90*day” with the End Date?

Now is a time, as is time stopped.
I’m on a phone here so I can’t test, but I would guess that end date is of type date, so you need to test against (current date - 90 * day).

1 Like

I think @TRN is bang on with his guess

image

1 Like

I think the complication in the properties is that ‘time stopped’ is an absolute time, while ‘end date’ and ‘end time’ are evaluated at the client based on the clients time zone or UTC.

You’ll probably need to grab the end date and end time, combine them with a time zone amd cast to a time object if you are comparing against ‘now’.

There’s probably an easier way though if you only care about days, to convert ‘now’ into a date and compare two dates rather than two times.

Just tested this:

	(
		ID of it , State of it , name of it , end date of it
	)
	of BES Actions 
	whose
	(
		(
			State of it as lowercase = "expired" 
		)
	and
		(
			Group Member Flag of it = false 
		)
	and
		(
			End Date of it < (current date - 90 * day )
		)
	)
1 Like

Forgot to post my final query on this. Thanks for the inspiration guys!

(ID of it, State of it, name of it) 
of BES Actions 
whose ( (Group Member Flag of it = false) and 
 ( ((State of it as lowercase = "expired") and (End Date of it < (current date - 20*day)) ) 
 or ((State of it as  lowercase = "stopped") AND (Time Stopped of it < now - 20*day)) ) 
)
3 Likes