QueryBuildRange to check start and end date

(2) Share

Report

Posted on by CU29041349-0

608

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)

Martin Dráb 238,097 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 238,097 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 238,097 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

Join the conversation

Community site session details

QueryBuildRange to check start and end date

Helpful resources

News and Announcements

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates

Community site session details

QueryBuildRange to check start and end date

Helpful resources

News and Announcements

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates