SQL Server sort by time plus one hour

I have a database of events each with a Time field that needs to be sorted in ascending order from said time. The "day" for these events start at 11:00PM (events take place 24 hours / day from 11:00PM to 10:59PM).

Is there a way to sort my events so that the results start with 11:00PM, 11:01PM, ..., 11:59PM events before continui开发者_Go百科ng with 12:00AM events? If examples could be provided in both SQL and LINQ to SQL that would be great.

Edit: I should explain the title of my question. My original idea was to just add an hour to each time while sorting but still have the returned value be the original time. This would cause all 11:00PM etc events to now be 12:00am etc events and everything else would be pushed ahead an hour (last events of the day at 10:59PM would now occur at 11:59PM for sorting purposes). Is this possible?

For a given set of times, sorting in ascending order will yield the same result as sorting (time + 1 hr) in ascending order. The order of a collection of values does not change when a constant value is added to each (in this & other simple cases, at least).

Do you additionally need to group on the date component, or fetch by date? I feel that perhaps your problem is how to group or select on (time + 1 hr), rather than sorting.

Not sure if this is the most efficient way but I think it is what you are asking for.

declare @theDateYouAreInterestedIn smalldatetime
-- this gets today's date and subtracts an hour giving you 11:00pm yesterday
-- if you want it some other date just sub out the Cast(GetDate() as date) for the
-- desired date
set @theDateYouAreInterestedIn = Dateadd(hour, -1,Cast(Cast(GetDate() as date) as smalldatetime))
select *
from yourTable
where yourDateTimeField > @theDateYouAreInterestedIn 
and yourDateTimeField < Dateadd(day, 1,@theDateYouAreInterestedIn)
order by yourDateTimeField