Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Finance forum / Adding a between Query...
Finance forum
Answered

Adding a between QueryBuildRange filter to a query

(1) ShareShare
ReportReport
Posted on by Superbunny 111
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:
Other
  • Superbunny Profile Picture
    Superbunny 111 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 111 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 225,864 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

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 . . .

more Community News

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 288,584 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,864 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans