CODEWITHSUNDEEP
t-sqlsql-server-2008
Michael Smit
Michael Smit

Reputation: 51

Date query in SQL2008

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

Upvotes: 1

Views: 171

Answers (3)

Beth
Beth

Reputation: 9607

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

Upvotes: 0

Elliveny
Elliveny

Reputation: 2203

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.

Upvotes: 1

Johnny DropTables
Johnny DropTables

Reputation: 656

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

Upvotes: 0

Related Questions