title: Finding correct shift based on time in SQL Server stored procedure
body: <p>I have 8 hrs shifts like this in my table <code>Shift</code> and <code>shiftstart</code> and <code>shiftend</code> datatype as <code>time(7)</code>.</p>
  
  <pre><code>ShiftNo ShiftName  ShiftStart  ShiftEnd  IsNextDay  IsBothNextDay
  --------------------------------------------------------------------
     1    Shift1     7:00:00     14:59:59   0          0
     2    SHift2     15:00:00    22:59:59   0          0
     3    Shift3     23:00:00    7:00:00    1          0
  </code></pre>
  
  <p>If I execute the procedure at 07:10, I should get shift3 row</p>
  
  <pre><code>23:00:00.0000000-07:00:00.0000000 as timestamp 
  </code></pre>
  
  <p>My existing procedure is </p>
  
  <pre><code>DECLARE @Currentdate AS DATETIME
  DECLARE @Currenttime AS TIME
  DECLARE @PreviousShifttime AS TIME
  
  SET @Currentdate = GETDATE()
  PRINT @currentdate  
  
  SET @Currenttime = (SELECT CAST(@Currentdate AS TIME))
  PRINT @Currenttime  
  
  SET @PreviousShifttime = (SELECT DATEADD(HOUR, -8, @Currentdate))
  PRINT @PreviousShifttime  
  
  SELECT 
      ShiftNo, ShiftName, ShiftStart, ShiftEnd, IsNextDay, IsBothNextDay 
  FROM
      ShiftInfo
  WHERE
      @PreviousShifttime BETWEEN ShiftStart AND ShiftEnd
  </code></pre>
  
  <p>This procedure is not returning the expected output when I want to get the row of shift 3. Output for this case is blank </p>
  
  <p>Can anyone tell me where I am going wrong?</p>