CODEWITHSUNDEEP
sqlsql-server-2012
Levi M
Levi M

Reputation: 65

Fetching records with same Id but between two given dates

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?

Upvotes: 0

Views: 180

Answers (3)

Simbarashe
Simbarashe

Reputation: 1

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

Upvotes: 0

Roman Marusyk
Roman Marusyk

Reputation: 24579

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'

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

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'

Upvotes: 0

Related Questions