Q:

T-SQL Overlapping time range parameter in stored procedure

I would like to search for records that occurred in a specific time of day and between a date/time range.
Example:
My table:
ID | EmpID |  AuthorizationTime
-------------------------------
1  | 21455 | '23/01/2012 12:44'
2  | 22311 | '23/01/2012 18:15'
3  | 21455 | '23/01/2012 23:04'
4  | 10222 | '24/01/2012 03:31'
5  | 21456 | '24/01/2012 09:00'
6  | 53271 | '25/01/2012 12:15'
7  | 10222 | '26/01/2012 18:30'
8  | 76221 | '27/01/2012 09:00'

Sample SP input parameters:
@from:  22/01/2012 08:00
@to:    24/01/2012 23:00
@fromtime:  18:30
@totime:    08:00

Expected Output:
EntryID EmployeeID  AuthorisationTime
3       21455       '23/01/2012 23:04'
4       10222       '24/01/2012 03:31'

I've tried the following select statements in the SP:
...
Select @wAuthorizationTime=' AuthorizationTime between ''' + CONVERT(nvarchar(30), @from )+ ''' and ''' + convert(nvarchar(50),@to )+ ''' '

Select @Where = @wAuthorizationTime; Declare @wHours nvarchar(1000)='';
    if (ISNULL(@fromtime,'')<>'' and ISNULL(@ToTime,'')<> '') begin Select @wHours= ' (Cast(AuthorizationTime as time) between ''' + @fromTime + ''' and '''+ @ToTime +''')' 
    end if (@wHours <> '') Select @Where=@Where + ' and ' + @wHours

...

The problem with this statement is that I'm not getting any results if the end time is lower than the start time (e.g. 23:00 to 03:00).
It does work if I use a time frame that doesn't overlap (e.g. 18:00 to 23:59).
What I need to do to get above results?

A:

This should give you what you want:
select *
from Times
where AuthorizationTime >= @from
and AuthorizationTime <= @to
and (
        (@fromtime > @totime and ((cast(AuthorizationTime as time) between '00:00:00' and @totime) or 
                                  (cast(AuthorizationTime as time) between @fromtime and '23:59:59.999')
                                 )
        ) or
       (@fromtime <= @totime and cast(AuthorizationTime as time) between @fromtime and @totime)
    )

SQL Fiddle

