Select date and time range when date and time are in two seperate columns

Question

I'm working on a database where the date and time is in two seperate columns and I'm tasked to show records between two dates and times intervals.

I have made the following query, but that gives me only the records from 10 to 12 on both dates. I'm missing the records from after 12 to 10 the next day.

How can I accomplish this?

SELECT 
 [guid], 
 [date], 
 [time], 
 [pos]
FROM
  SomeTable
WHERE 
  [guid] = '0Q8m48D_uHua6P0' 
  AND [date] >= '2017-09-12' 
  AND time >= '10:00' 
  AND [date] <= '2017-09-13' 
  AND time <= '12:00';

Combine them -> cast them to DateTime -> use `BETWEEN` . If you are making where clauses based on the date, you should store the full `DateTime` in its own column, as it is much easier to handle. — sagi, Sep 14 '17 at 13:38
I think the best would be to have a new computed column that concatenates the date and the time, so that you can filter on the new datetime column. — Rigerta, Sep 14 '17 at 13:38
@sagi I am curios to learn casting in this particular question please can you post with an example. — Chetan_Vasudevan, Sep 14 '17 at 13:39
See this question for examples on combining date and time to get a single date - [How to combine date and time to datetime2 in SQL Server?](https://dba.stackexchange.com/q/51440/7257) — GarethD, Sep 14 '17 at 13:40

cloudsafe · Accepted Answer · 2017-09-14T14:28:47.200

1

Have a clause for beginning and end date exceptions:

SELECT [guid], [date], [time], [pos]
FROM [table]
WHERE [guid] = '0Q8m48D_uHua6P0' 
and [date] >= '2017-09-12' AND [time] >= iif([date] = '2017-09-12', '10:00', [time])
AND [date] <= '2017-09-13' AND [time] <= iif([date] = '2017-09-13', '12:00', [time]);

edited Sep 14 '17 at 14:28

answered Sep 14 '17 at 13:41

cloudsafe

2,444
1
8
24

1

@sagi Can you explain why, please? – cloudsafe Sep 14 '17 at 13:45
Hard maintenance, easy to make errors. (Its not my downvote BTW) – sagi Sep 14 '17 at 13:49
1

@sagi That is too vague to help me make improvements. – cloudsafe Sep 14 '17 at 13:51
I don't think there's anything to improve, I just think other solutions like provided above are the correct way of solving this problem . These kind of solutions are like a bandage due to of wrong design , and a developer can easily get confused and enter wrong dates. – sagi Sep 14 '17 at 13:53
@sagi I disagree and still don't know what your point is. Also, this method is sargable, so will be more efficient, being able to better use the indexes.The methods above are harder to maintain and no less prone to errors. Please explain yourself so I can understand why you think this is not the correct method. If you can't, can you remove the down arrow, please. – cloudsafe Sep 14 '17 at 13:56
No. Your query won't be faster . This is the correct solution : https://stackoverflow.com/a/46220916/5353753 . His design is wrong, and he should change it. That is my point. – sagi Sep 14 '17 at 13:58
@sagi If you are changing the data structure, then there should only be a datetime column. Databases have legacy problems we have to live with sometimes. Revisiting design is not always viable. – cloudsafe Sep 14 '17 at 14:06
@Sagi As I mentioned: this method is sargable and the other methods above are not, so this will be faster: http://www.sqlinthewild.co.za/index.php/2016/09/13/what-is-a-sargable-predicate/ – cloudsafe Sep 14 '17 at 14:13
2

This answer will perform better than the others (at the time of writing), but in its current format it is not correct. You are excluding records from the last minute of every day. Something with a combined date and time of `20170912 23:59:59` would be missed. You need to change `23:59:00` to `23:59:59.99999999` – GarethD Sep 14 '17 at 14:25
@GarethD Thanks. Should not have assumed it had restricted to minutes. I have changed the literal comparison for the column 'time' – cloudsafe Sep 14 '17 at 14:30
@GarethD This will perform better then an indexed DateTime column using between ? – sagi Sep 14 '17 at 14:35
@cloudsafe chill, my entire point was -> Don't use bandages , correct your design . I didn't downvote you . I don't see how having two columns for date and time is a good design. Just like I mentioned in my comment before you answered the question. – sagi Sep 14 '17 at 14:38
I delete my comment of "bad bad practice" (which I still stand by!!), which was the first comment because this conversation is getting no where. You can't edit comment after more than 4 minutes. "Denial? LOL, I'll downvote you now just to prove it then. – sagi Sep 14 '17 at 14:44
@sagi I understand this got heated but please adhere to the etiquette of StackOverflow and downvote on the merits of solutions, not if someone gets up your nose ;-) – cloudsafe Sep 14 '17 at 14:56
1

I removed my downvote 10 minutes ago, just 1 minute after I showed you I didn't "deny" it, I didn't do it. Let's move on. – sagi Sep 14 '17 at 14:58
@sagi No this won't perform better than an indexed datetime column, however, none of the other answers have suggested this. The closest is Rigerta Demiri's answer, which suggests a persisted computed column, which suggests a persisted computed column, but there is no mention of an index, and the expression in its current form is not deterministic, so would not allow an index. – GarethD Sep 14 '17 at 15:06
In some situations, I can see that separate Date and Time columns could have performance advantages. Querying on a Time column between 10 and 12, regardless of Date, would be far faster than using a datetime field. Similarly Date fields are more efficient than datetime (not having to extract the date and lose sarg or specify BETWEEN that day and the next). It is perfectly acceptable to have the OP's columns in a table for some purposes. It is not necessarily bad design. – cloudsafe Sep 14 '17 at 15:30

Rigerta · Answer 2 · 2017-09-14T14:25:57.847

You could of course combine them and then use BETWEEN to filter, like this:

SELECT 
 [guid], 
 [date], 
 [time], 
 [pos]
FROM
  SomeTable
WHERE [guid] = '0Q8m48D_uHua6P0' 
  AND (DATEADD(day, DATEDIFF(day,'19000101', [date]), CAST([time] AS DATETIME2(7)))) BETWEEN '2017-09-12 12:00:00.000' and '2017-09-12 12:00:00.000'

..but this is a bad idea, since you have a function on your conditions, which ~~will~~ might make the query optimizer ignore any indexes you might have in place (since such a query is not sargable)

So, I would create a new persisted computed column on your table called [Datetime] or anything that makes sense to you and then give it a default value as:

DATEADD(day, DATEDIFF(day,'19000101', [date]), CAST([time] AS DATETIME2(7))))

Then you would be able to write the query as follows:

SELECT 
 [guid], 
 [date], 
 [time], 
 [pos]
FROM
  SomeTable
WHERE [guid] = '0Q8m48D_uHua6P0' 
  AND [Datetime] BETWEEN '2017-09-12 12:00:00.000' and '2017-09-12 12:00:00.000'

score 0 · Answer 3 · answered Sep 14 '17 at 13:42

You can compute a column as DATETIME and then filter on it.

;with cte as (
    SELECT 
     [guid], 
     [date], 
     [time], 
     [pos],
     DT = cast([date] as datetime) + cast([time] as datetime)
    FROM
      SomeTable
    WHERE 
      [guid] = '0Q8m48D_uHua6P0'
)

select * 
from cte
where DT between '20170912 10:00' and '20170913 12:00'

score 0 · Answer 4 · answered Sep 14 '17 at 13:46

Here is one way of doing this

SELECT 
[guid], 
[date], 
[time], 
[pos]
FROM
 SomeTable
WHERE 
 [guid] = '0Q8m48D_uHua6P0' 
AND
CAST   (
CONVERT(Varchar(10), [date], 112) + ' ' +   
CONVERT(Varchar(8), [time]) AS DateTime) Between '2017-09-12 10:00' 
AND '2017-09-13 12:00'

Select date and time range when date and time are in two seperate columns

4 Answers4