Date query in SQL2008

Question

Hi All,

I have the following records depicting shifts in the Shifts table.

ID, Description, Start Time, End Time
1, Morning, 06:00, 13:59
2, Afternoon, 14:00, 21:59
3, Night, 22:00, 05:59

I now need to be able to get the shift relevant to a passed time but get stuck with getting the record for the night shift where the time starts before midnight and ends the following day.

What is the easiest way to query this table to get the correct shift based on a passed time?

TIA - Mike

Answer 1

If you used a Datetime, it would include the date, you could then query the entire datetime and easily handle the result

Answer 2

The SQL 2008 time types might improve it slightly, but try this:

--Your Shift data as a temp table
declare @shifts table (ID int, Description varchar(10), [Start Time] smalldatetime, [End Time] smalldatetime)
insert into @shifts (ID, Description, [Start Time], [End Time])
select 1, 'Morning', '06:00', '13:59'
union all
select 2, 'Afternoon', '14:00', '21:59'
union all
select 3, 'Night', '22:00', '05:59'

-- Time to check
declare @timeToCheck smalldatetime
SET @timeToCheck='04:00'

-- The query to find the matching shift
select * from @shifts where 
([Start Time]<[End Time] AND @timeToCheck>=[Start Time] AND @timeToCheck<=[End Time]) OR 
([Start Time]>[End Time] AND (@timeToCheck>=[Start Time] OR @timeToCheck<=[End Time]))

Change the @timeToCheck to test it.

Answer 3

you shouldn't need to store the end time, it's implied by the next start time. if it makes it easier, you could have a second table with shift time ranges with a one-to-many relationship with shifts.

in the second table, add a fourth row with shift 3 ranging from 0 -> 5:59. so table1 has 3 rows, table2 has 4 like this:

shiftID shiftTime 
3       00:00 
1       06:00 
2       14:00 
3       22:00

if you want, you can add another column named isShiftStart marked true for times 06, 14, and 22, and false for time 00:00