Fetching records with same Id but between two given dates

Question

I have a table like below:

MyTable

FileNo: |  amount | date
---------------------------------------------------
111     | 55      | 2017-04-07 04:51:45.870
111     | 55      | 2017-04-08 04:51:45.870
111     | 55      | 2017-04-09 04:51:45.870
111     | 55      | 2017-04-10 04:51:45.870
999     | 55      | 2017-04-11 04:51:45.870
111     | 55      | 2017-04-12 04:51:45.870
999     | 55      | 2017-04-13 04:51:45.870
111     | 55      | 2017-04-14 04:51:45.870
999     | 55      | 2017-04-15 04:51:45.870

Now I want to fetch records where FileNo: is '111' and date between '04/07/2017' and '04/10/2017'

I have written my query like below:-

select * from MyTable where FileNo: = 111 and date >= '04/07/2017' and date <= '04/10/2017'

But it didn't work, there was no error found, It didn't return any records, it just showed empty table!, is my syntax wrong or am I missing 'Group By' or any other Clause?

Possible duplicate of [Select data from date range between two dates](http://stackoverflow.com/questions/14208958/select-data-from-date-range-between-two-dates) — Mederic, May 22 '17 at 11:44

score 1 · Accepted Answer · answered May 22 '17 at 11:45

1

If it is MSSQL Server then try

SELECT * FROM MyTable 
WHERE FileNo: = 111
  AND CAST(date AS DATE) BETWEEN '2017-07-04' AND '2017-10-04'

answered May 22 '17 at 11:45

Roman Marusyk

23,328
24
73
116

Glad to have been of help – Roman Marusyk May 23 '17 at 08:11

score 0 · Answer 2 · answered May 22 '17 at 11:44

0

Your date literals are in a wrong format. Try using the ANSI standard yyyy-MM-dd instead:

select *
from MyTable
where FileNo: = 111 and
      date between '2017-07-04' and '2017-10-04'

answered May 22 '17 at 11:44

Tim Biegeleisen

502,043
27
286
360

score 0 · Answer 3 · edited May 22 '17 at 13:31

select * from MyTable where FileNo: = 111 and date >= '04/07/2017' and date <= '04/10/2017'

This query is correct thus why you haven't seen any error's. However, no dates will be matched.

Please note that:

2017-04-07 04:51:45.870 = 07 April 2017; and
04/10/2017 = 04 October 2017

So you will never expect to see any results there.

Solution

select * from MyTable where FileNo: = 111 and date >= '2017-04-07' and date <= '2017-04-15'

As well remove the time use only date ie yyyy-MM-dd NOT yyyy-MM-dd hhmmss

Fetching records with same Id but between two given dates

MyTable

3 Answers3