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
