Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Adding a between Query...
Finance | Project Operations, Human Resources, ...
Answered

Adding a between QueryBuildRange filter to a query

(1) ShareShare
ReportReport
Posted on by Superbunny 294
I am trying to add a SQL BETWEEN filter via queryBuildRange.
I have the following code:
 
Query queryHoursRegistered = new Query(queryStr(FM_TimeCardTotalHoursRegistered));
WeekDay weekDayNr = dayofwk(currentProfileDate);                  
WeekDay daysUntilWeekend = 7-weekDayNr;
JmgProfileDate firstDayOfWk = currentProfileDate - (weekDayNr-1);
JmgProfileDate lastDayOfWk = currentProfileDate + daysUntilWeekend;
QueryBuildRange qbr2 = queryHoursRegistered.dataSourceTable(tableNum(JmgTimecardTrans)).addRange(fieldNum(JmgTimecardTrans, ProfileDate));
qbr2.value(strFmt('(( %1.%2 >= %3) && (%1.%2 <= %4))',qbds.name(),fieldStr(JmgTimecardTrans, ProfileDate),firstDayOfWk,lastDayOfWk));
 
Using this, I hope to create the following SQL query:

SELECT SUM(Seconds), MAX(ProfileDate) 
FROM JmgTimecardTrans  WHERE ((JourRegType = 5) OR (JourRegType = 7) OR (JourRegType = 8) OR (JourRegType = 12) OR (JourRegType = 13)) AND ((Worker = 5637148357)) 
AND (((( ProfileDate >= '9/25/2023') and (ProfileDate <= '10/1/2023'))))
GROUP BY JmgTimecardTrans.ProfileDate, JmgTimecardTrans.Worker
 
This query returns 1 record in SQL Server Management Studio.
The query object in X++ contains the following semi-sql query:

{
Query FM_TimeCardTotalHoursRegistered object 9d9822c0: SELECT SUM(Seconds), MAX(ProfileDate) FROM JmgTimecardTrans(JmgTimecardTrans) GROUP BY JmgTimecardTrans.ProfileDate, JmgTimecardTrans.Worker WHERE ((JourRegType = 5) OR (JourRegType = 7) OR (JourRegType = 8) OR (JourRegType = 12) OR (JourRegType = 13)) AND ((Worker = 5637148357)) AND (((( JmgTimecardTrans.ProfileDate >= 9/25/2023) && (JmgTimecardTrans.ProfileDate <= 10/1/2023))))
}
 
This looks to me correct, but somehow it does not return data when ran this way. The only thing I noticed is that the brackets are missing around dates, but this could be correct.
I tried to solve the issue changing the queryString for the BETWEEN clause to:
 
(( %1.%2 >= ///%3///) && (%1.%2 <= ///%4///))
This gives an error: Can't convert string to date
 
(( %1.%2 >= //'%3//') && (%1.%2 <= //'%4//'))
This gives an error: Can't parse parameter
 
Does anyone know what is wrong with my query setup or why I would not receive the same data in the application as in SSMS.
P.S. I checked that the data belongs to the correct dataAreaId, so that is not causing the problem 
  • Superbunny Profile Picture
    Superbunny 294 on at
    Adding a between QueryBuildRange filter to a query
    UPDATE:  The solutions worked. The issue was that the month and day were swapped in the query to SQL. While testing this the second time I conincidentally had a day and month, both in the range 1-12, so I never received an error.
    Instead of selecting a week in oktober I was selecting an earlier date range which did not contain any data.
  • Superbunny Profile Picture
    Superbunny 294 on at
    Adding a between QueryBuildRange filter to a query
    I tried the given solutions, but still do not see any data.
     
    Both of these solutions give the same query in the SQL profiler:
     
     
    1. qbr2.value(strFmt('(( %1.%2 >= %3) && (%1.%2 <= %4))',qbds.name(),fieldStr(JmgTimecardTrans, ProfileDate),date2StrXpp(firstDayOfWk), date2StrXpp(lastDayOfWk)));
    2. QueryBuildRange qbr2 = queryHoursRegistered.dataSourceTable(tableNum(JmgTimecardTrans)).addRange(fieldNum(JmgTimecardTrans, ProfileDate));
                qbr2.value(SysQuery::range(firstDayOfWk, lastDayOfWk));
     
    The returned query is:

    SELECT SUM(T1.SECONDS),MAX(T1.PROFILEDATE),T1.PROFILEDATE,T1.WORKER 
    FROM JMGTIMECARDTRANS T1 
    WHERE (((PARTITION=5637144576) AND (DATAAREAID=N'100')) 
    AND ((((((JOURREGTYPE=5) OR (JOURREGTYPE=7)) OR ((JOURREGTYPE=8) OR (JOURREGTYPE=12))) OR (JOURREGTYPE=13)) 
    AND (WORKER=5637148357)) 
    AND ((PROFILEDATE>='2023-10-02 00:00:00') AND (PROFILEDATE<='2023-10-08 00:00:00')))) 
    GROUP BY T1.PROFILEDATE,T1.WORKER ORDER BY T1.PROFILEDATE,T1.WORKER
     
    This query returns nothing, but gives no error. Simply changing the date filter from:
    "AND ((PROFILEDATE>='2023-10-02 00:00:00') AND (PROFILEDATE<='2023-10-08 00:00:00')))) "
    to
    "AND (((( JmgTimecardTrans.ProfileDate >= '02/10/2023') AND (JmgTimecardTrans.ProfileDate <= '08/10/2023'))))"
    fixes my issue. So the problem seems to be the date format created in the SQL query.
     
    How can I change this?
     
  • Suggested answer
    SashaCodes Profile Picture
    SashaCodes 13 on at
    Adding a between QueryBuildRange filter to a query
    Their is a small problem, the way you are constructing the query for X++: Below is teh code I have a recreated 
     
    Query queryHoursRegistered = new Query(queryStr(FM_TimeCardTotalHoursRegistered));
    WeekDay weekDayNr = dayofwk(currentProfileDate);                  
    WeekDay daysUntilWeekend = 7 - weekDayNr;
    JmgProfileDate firstDayOfWk = currentProfileDate - (weekDayNr - 1);
    JmgProfileDate lastDayOfWk = currentProfileDate + daysUntilWeekend;
    QueryBuildRange qbr2 = queryHoursRegistered.dataSourceTable(tableNum(JmgTimecardTrans)).addRange(fieldNum(JmgTimecardTrans, ProfileDate));
    qbr2.value(strFmt("((%1.%2 >= '%3') && (%1.%2 <= '%4'))", qbds.name(), fieldStr(JmgTimecardTrans, ProfileDate), date2str(firstDayOfWk, 123), date2str(lastDayOfWk, 123)));
     
    firstDayOfWk and lastDayOfWk in single quotes to ensure they are treated as strings in the SQL query and use date2str.
     
  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 233,011 Most Valuable Professional on at
    Adding a between QueryBuildRange filter to a query
    You should avoid the extended query range syntax (things like '(( %1.%2 >= %3) && (%1.%2 <= %4))') whenever possible. As you see, it's difficult to use and debug.
     
    In your case, simply use queryRange() method. That's much easier.
     
    If you had to use your the string expression, the right way of converting dates is using date2StrXpp().

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

🌸 Community Spring Festival 2025 Challenge 🌸

WIN Power Platform Community Conference 2025 tickets!

Jonas ”Jones” Melgaard – Community Spotlight

We are honored to recognize Jonas "Jones" Melgaard as our April 2025…

Kudos to the March Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 294,261 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 233,011 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,158 Moderator

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans