0

here's my recordset:

id   date
-----------------------
1    2017-01-11
2    2017-01-12
3    2017-01-14
4    2017-01-15
4    2017-01-16

i'd like to query all records within the date range 2017-01-14 to 2017-01-16

currently i'm using:

SELECT * FROM foo WHERE (date='2017-01-14' OR date='2017-01-15' OR date='2017-01-16')

is there a better way (which would be probably faster for bigger ranges)? thanks

PS: i'm aware i could use:

SELECT * FROM foo WHERE date >= '2017-01-14' AND date <= '2017-01-16'

but the problem is that i don't want "gaps" between each day.

shmosel
  • 49,289
  • 6
  • 73
  • 138
Fuxi
  • 7,611
  • 25
  • 93
  • 139
  • use between **SELECT ... WHERE date BETWEEN '2017-01-14' AND '2017-01-17';** – Bernd Buffen Jan 11 '17 at 22:45
  • Gaps? What sort of gaps? – shmosel Jan 11 '17 at 22:46
  • i mean as shown in my data: there's 11,12,13 (is missing), 14, .. so the query would be incorrect – Fuxi Jan 11 '17 at 22:47
  • I'm not following. You're not even querying for 13. And what would be incorrect? – shmosel Jan 11 '17 at 23:21
  • sorry, more specific: i want to query all record-"sets" eg. between 2017-01-12 and 2017-01-15 with ALL dates set (12,13,14,15) which should return 4 records, but as 13 is missing it will only return 3. so the query also needs to consider the amount of records i guess (using datediff?) – Fuxi Jan 11 '17 at 23:26
  • 1
    Yuor problem isn't with selecting the date range, your problem is with adding the missing dates. – Barmar Jan 11 '17 at 23:42

1 Answers1

0
SELECT *
  FROM foo
 WHERE date BETWEEN '2017-01-14' AND '2017-01-16';
shmosel
  • 49,289
  • 6
  • 73
  • 138
  • How does this solve the problem of the missing date `2017-01-13`? – Barmar Jan 11 '17 at 23:41
  • @Barmar This was in response to the original question, which mentioned nothing about gaps. Furthermore, it's fully equivalent to the original query, which was implied to be functionally correct. – shmosel Jan 11 '17 at 23:59
  • It's not my question. I'd like to remove my downvote, but I can't unless you edit the answer. But maybe you should just delete it, since the question was edited and it's no longer useful. – Barmar Jan 12 '17 at 00:00