Hello César.
César Parrales said:
what was it that separates the man from the boys? Owerpowering?
I need to select the current shift from a table. The table has
3 shifts stored:
ID Start End
1 07:00:01 am 03:00:00 pm
2 03:00:01 pm 11:00:00 pm
3 11:00:01 pm 07:00:00 am
I need to select the current shift regarding to the current time.
Well, the problem is that 11:30 pm lies before 07:00 am on your
clockwise circle, but "comparison" in a computer normally dosn't
work in a closed circle.
btw: what shift is between 07:00:00 and 07:00:01?
You could do the following:
Break down the night shift into two records, maybe in another table:
RangeID SHiftID Start End
1 1 07:00 am 03:00 pm
2 2 03:00 pm 11:00 pm
3 3 11:00 pm 00:00 am
4 3 00:00 am 07:00 am
(the End time in the third record was entered as 12/31/1899 but is
formatted as time, so it's "0:00 the next day)
Then use:
PARAMETERS [Time] DateTime;
Select ShiftID From MyTable Where [Time] < End And [Time] >= Start;
I added a parameter for DateTime to avoid the usage of the time()
function, because when the first criterion (Time()<End) is eveluated
shortly before midnight such that when evaluating the second
criterion, the date has changed, such a query would return no record.
You can of course add a calculated field Time: Time() to your queries
and use the [Time] field for the comparisons.
