QueryBuildRange to check start and end date

(2) Share

Report

Posted on by CU29041349-0

548

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)

Verified answer

Martin Dráb 236,017 Most Valuable Professional on at

Like (2)

Report
QueryBuildRange to check start and end date

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
Suggested answer

Martin Dráb 236,017 Most Valuable Professional on at

Like (2)

Report
QueryBuildRange to check start and end date

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
Martin Dráb 236,017 Most Valuable Professional on at

Like (1)

Report

QueryBuildRange to check start and end date

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

Community site session details

QueryBuildRange to check start and end date

Helpful resources

News and Announcements

Quick Links

Responsible AI policies

Andrés Arias – Community Spotlight

Congratulations to the August 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

Andrés Arias – Community Spotlight

Congratulations to the August Top 10 Community Leaders!

Subscribe to this forum!

Select categories

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

Featured topics

Product updates