SQL Server – How to Return Rows Between Two Datetimes

sql server

How to view the result between 2 dates and time ?

My current Code:

SELECT * 
FROM [DB].[dbo].[TABLE] 
WHERE [USER_ID] = '005'    
AND [DATE] BETWEEN '03-04-2016' AND '04-04-2016'
AND [TIME] >= '21:00:00'                   -- for date 03-04-2016
AND [TIME] <= '05:00:00'                   -- for date 04-04-2016

ORDER BY [DATE] ASC, [TIME] ASC

This code does not work and I do not know how to fix it

why doesn't this request work ?

Best Answer

It is impossible for any row to satisfy both [TIME] >= '21:00:00' and [TIME] <= '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] >= '21:00:00' OR [DATE] > '20160403')
AND ([TIME] <= '05:00:00' OR [DATE] < '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] >= '21:00:00'
  -- OR [DATE] > '20160403' AND [DATE] < '20160404'
   OR [DATE] = '20160404' AND [TIME] <= '05:00:00'
    ) ;
Related Question