Skip to main content

Notifications

Announcements

No record found.

Dynamics 365 Community / Forums / Finance forum / QueryBuildRange to che...
Finance forum
Answered

QueryBuildRange to check start and end date

Posted on by 158
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. 
  • CU29041349-0 Profile Picture
    CU29041349-0 158 on at
    QueryBuildRange to check start and end date
    You're a lifesaver, Martin. Thank you very much. 
  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 225,490 Super User on at
    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()));
  • CU29041349-0 Profile Picture
    CU29041349-0 158 on at
    QueryBuildRange to check start and end date
    If I may ask one more thing, on many of the records the ActiveTo and ActiveFrom fields default to a 1900-01-01 especially for ActiveTo. How would I check ActiveTo is this 1900-01-01 date OR ActiveTo is greater than or equal to today?
  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 225,490 Super User on at
    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));
  • CU29041349-0 Profile Picture
    CU29041349-0 158 on at
    QueryBuildRange to check start and end date
    Hi Martin,
    The table is DimensionAttrValueLedgerOverride and that property is None. 
  • Martin Dráb Profile Picture
    Martin Dráb 225,490 Super User on at
    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.

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 287,696 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,490 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans