QueryBuildRange to check start and end date

(2) Share

Report

Posted on by CU29041349-0

628

Hello,

I have a record with ActiveFrom and ActiveTo dates.

I'm writing a query using the query object but I'm stuck. How do I use a BuildQueryRange to check if the ActiveFrom is less than or equal to today, and of course if ActiveTo is greater than or equal to today. I see a lot of reference to using SysQuery::Range() but I'm not sure how that would work for my scenario.

I have the same question (0)

All responses (3)

Answers (1)

Sort by

Martin Dráb 240,538 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

Is it a date-effecive table? If you don't know, check its Valid Time State Field Type property.

Was this reply helpful? Yes No
Suggested answer

Martin Dráb 240,538 Most Valuable Professional on at

Like (2)

Report
Copy link

Link copied!
Using SysQuery::range() or queryRange() (which is a shorter way of the same) is a good idea. One of the operands is clear - you'll use the current date. Use null for the other, which won't set any boundary there (therefore it'll be some like from:today to:unlimited). Here is some code:
date today = DateTimeUtil::getToday(DateTimeUtil::getUserPreferredTimeZone()); ds.addRange(fieldNum(DimensionAttrValueLedgerOverride, ActiveFrom)).value(queryRange(null, today)); ds.addRange(fieldNum(DimensionAttrValueLedgerOverride, ActiveTo)).value(queryRange(today, null));

Was this reply helpful? Yes No
Verified answer

Martin Dráb 240,538 Most Valuable Professional on at

Like (2)

Report
Copy link

Link copied!
Don't you want to say that "ActiveTo is either 1900-01-01 or greater than or equal to today" instead of "ActiveTo is not this 1900-01-01 date and ActiveTo is greater than or equal to today"?

All values greater than today are surely not equal to 1900-01-01. Accepting 1900-01-01 (= no date) means that you understand it as no limit on ActiveTo.

If you agree with my definition, then you can simple add one more range:

ds.addRange(fieldNum(DimensionAttrValueLedgerOverride, ActiveTo)).value(queryValue(dateNull()));

Was this reply helpful? Yes No