Return a Time column from SQL

I’m trying to create a Schedule software to count parts produced at line.

I have a table with name schedule, with the datetime column of startdatetime and enddatetime of shift.(DateTime)
Also have a table break, with time of break. With Start time and End time.(Time)

I’m trying to start a counter when the actual time from gateway is >= the startdatetime and <= enddatetime. Works great, But when trying to include another IF for the date and time need to be between the schedule and the hour diff from the break star and end time.

I try to use a dateFormat, but show a wrong time.

Has another option?


Post your query using triple backquotes one line before and one line after your code to format it and be readable.


This formula looks from a Break Time from DATASET.

timeBetween(dateFormat(now(),“HH:mm:ss”),lookup({[.]Break Data},{[.]ShiftNumber},0,“ShiftNumber”,“StartDate”),lookup({[.]Break Data},{[.]ShiftNumber},0,“ShiftNumber”,“EndDate”))

And the dataset was:

SELECT ID, StartDate, EndDate, WorkGroup, ShiftNumber FROM BZbreak WHERE WorkGroup = ‘{[.]WorkGroup}’ AND ShiftNumber = ‘{[.]ShiftNumber}’