How to return rows between two datetimes stored as date and time?

Question

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 ?

Answer 1

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'
    ) ;

Answer 2

The date and time are not related to each other, SQL has no way to know that you mean between '20160403 21:00:00' and '20160404 05:00'.

You need to combine the two to a DATETIME type ( DateTimeFromParts would do nicely ) add that as a computed column , potentially indexed if you have a big table, then you can :

SELECT * FROM [DB].[dbo].[TABLE] 
WHERE [USER_ID] = '005'
AND [DATETIME] BETWEEN '20160403 21:00:00' AND '20160404 05:00:00 '
ORDER BY [DATETIME ] ASC

Answer 3

You can combine your Date and Time columns into DateTime2 type and then use it for between search:

SELECT  * 
FROM    [DB].[dbo].[TABLE] 
WHERE   [USER_ID] = '005'    
        AND DATEADD(DAY, 
                    DATEDIFF(DAY, '19000101', [DATE]), 
                    CAST([TIME] AS DATETIME2(7))
                   ) 
            BETWEEN '03-04-2016 21:00' 
                AND '04-04-2016 05:00'
ORDER BY [DATE] ASC, [TIME] ASC

The CAST to DATETIME2(7) converts the TIME(7) value ([TIME]) to a DATETIME2 where the date part is '1900-01-01', which is the default value of date and datetime types (see datetime2 and CAST and CONVERT page at MSDN.)

The DATEADD() and DATEDIFF()function take care of the rest, i.e. adding the difference in days between the 1900-01-01 and the DATE value ([DATE]).

(Inspired by @ypercube's answer here)