Skip to main content
Dynamics 365 Community / Forums / Finance forum / Adding a between Query...
Finance forum
Answered

Adding a between QueryBuildRange filter to a query

editSubscribe (1) ShareShare
ReportReport
Posted on by 105
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 
Categories:
  • Superbunny Profile Picture
    Superbunny 105 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 105 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
    Community member Profile Picture
    Community member 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 222,570 Super User 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().

Helpful resources

Quick Links

What Motivates a Super User?

We know many of you visit the Dynamics 365 Community and Power Platform…

Demystifying Copilot with Georg Glantschnig…

Industry experts answer burning questions directly from our amazing Community…

Setting Up Knowledge Sources for Copilot…

Look at how configuring a comprehensive knowledge base is crucial…

Leaderboard

#1
Andre Arnaud de Calavon Profile Picture

Andre Arnaud de Cal... 283,045 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 222,570 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,138

Product updates

Dynamics 365 release plans