How to return rows between two datetimes stored as date and time?
It is impossible for any row to satisfy both [TIME] &gt;= '21:00:00' and     [TIME] &lt;= '05:00:00'

SQL server doesn't read your source code comments to know that the time should be applied conditionally.

You need to change the code in order to apply these predicates (which will be residual predicates assuming a seek on date) conditionally on the boundary dates so the time element is only relevant on the days these conditions apply to.   

SELECT *
FROM [DB].[dbo].[TABLE] 
WHERE [USER_ID] = '005'   
AND [DATE] BETWEEN '20160403' AND '20160404'
AND ([TIME] &gt;= '21:00:00' OR [DATE] &gt; '20160403')
AND ([TIME] &lt;= '05:00:00' OR [DATE] &lt; '20160404') ;


or the equivalent (perhaps more self-documenting though you would need to check the execution plans for efficiency):

SELECT *
FROM [DB].[dbo].[TABLE] 
WHERE [USER_ID] = '005'   
AND ( [DATE] = '20160403' AND [TIME] &gt;= '21:00:00'
  -- OR [DATE] &gt; '20160403' AND [DATE] &lt; '20160404'
   OR [DATE] = '20160404' AND [TIME] &lt;= '05:00:00'
    ) ;