Strange value given as a result of my query in my report

As the title states I’m getting a result from my query that I didn’t expect at all. But it isn’t data, and the same query does return what I expected it to every other time except for the last row in the report.

Here is my query:

select ID, m.project, 
DATEDIFF(hour, p.user_starttime, p.user_stoptime) as hours, 
DATEDIFF(minute, p.user_starttime, p.user_stoptime) as minutes, 
DATEDIFF(second, p.user_starttime, p.user_stoptime) as seconds 
from masterfabparts2 m, partsworkedon p where m.rw_date <= ? and m.rw_date >= ? and = p.part_id_mf

Where my parameters are weekBefore and today. These parameters are set up thusly;

weekBefore: dateArithmetic(now(), -7, "days")
today: now()

The design of the table looks like this:

The design preview of the table looks like this:

If anyone has any insight into why this report is generating that way, or at the very least why it isn’t throwing a data-collection error like it usually does if my query is bad, I would really value your insight.

EDIT: Query has been changed slightly because I had noticed an error in what DATEDIFF() was actually doing compared to what I thought it was doing. The only result of this change is that hh:mm:ss is formatted and displayed correctly now. The last row, however, still results in an N/A half the time and this strange string the other half, and I have no idea why.

Ahaha, well. This is awkward. The final entry in the data is displaying a bugged result because there isn’t any data for it to pull from there despite the query resulting in “True” and thus causing no data collection error.

This particular test part was never “clocked out” of the shop, and had no end date.

To fix this all I need to do is write some “Case” logic to ensure that the only parts that are displayed are the ones with both a start-date and an end-date.

Hope this helps someone out of if they stumble on a similar issue.

1 Like