Jeff Moden (9/16/2012)

---

Stefan Krzywicki (9/14/2012)

---

I have two fields of type time. StartTime and EndTime. I want to see if Now is between these two times. For most of the records in this table, there's no problem

DECLARE @Now time = CAST(GetDate() as time)

SELECT LoadNumber FROM LoadTimes WHERE @Now >=StartTime AND @Now < EndTime

I'm having a problem with one row though.

CREATE TABLE LoadTimes(

LoadNumber tinyint,

StartTime time,

EndTime time

)

INSERT INTO LoadTimes(LoadNumber, StartTime, EndTime)

VALUES(1, '18:00', '21:00'),

(2, '21:00', '1:00'),

(3, '1:00', '18:00')

The period for Load Number 2 starts at 9PM and ends at 1AM. How do I search for that? If I state

SELECT LoadNumber FROM LoadTimes WHERE @Now >= StartTime OR @Now < EndTime

I end up with the whole table in my results. I can't add 24 to the end time or to Now because the time data type doesn't accept 24:00 and up. My values are all whole hours now, but might not be in the future, so I can't just strip to integers and I want to know if there's a way to do this with time anyway. I suppose I could figure make it integers, but still I'd like to know if there's a way to do it with the time type.

Do the start and end times have dates associated with them?