Not able to fetch records by Date from database

Question

I have data based on column id and date...Data Type for date is DATETIME

DataBase : sql server 2008

Query 1:

Select * 
from table t1 
where (t1.date between '2016-05-11' and  '2016-05-13')

Query 2:

select * 
from table t1 
where t1.date IN ('2016-05-11')

Result is NUll even i have records on this date

So is there any Alternative to fetch records except (<=/</>/>=) greater than or less than

please use Select * from table t1 where (t1.date between '2016-05-11' and '2016-05-13') — Chetan Sanghani, May 12 '16 at 10:20
refer to http://stackoverflow.com/questions/10643379/how-do-i-query-for-all-dates-greater-than-a-certain-date-in-sql-server — Wajih, May 12 '16 at 10:21
@ChetanSanghani your query is ame as Query 1,and i am not getting any records — Vivek Gondliya, May 12 '16 at 10:25

score 0 · Accepted Answer · answered May 12 '16 at 10:27

0

You can use below query but your query is also correct and its running on my server.

Select * from table t1 
where t1.date between '20160511 00:00:00' and '20160513 23:59:59';

select * from table t1 where cast(t1.date as date) IN ('20160511');

answered May 12 '16 at 10:27

Sandeep Kumar

1,172
1
9
22

it is working if I have Data Type "date" in database....but not working with Data Type "datetime" – Vivek Gondliya May 12 '16 at 11:02
Which server version you are using? – Sandeep Kumar May 12 '16 at 11:03
sql server 2008 R2 – Vivek Gondliya May 12 '16 at 11:07
On SQL server 2008 R2 , I have tested and its running. – Sandeep Kumar May 12 '16 at 11:10

Abdul Rasheed · Answer 2 · 2016-05-12T11:19:28.233

0

Try this,

Select * 
from table t1 
where cast(t1.date as date) between '2016-05-11' and  '2016-05-13'

ie Convert your datetime column to date and compare, because your parameters are only date values. or

where cast(t1.date as date) in ('2016-05-11')--For your second query

or use greater than or less than

Select * 
from table t1 
where t1.date >= '2016-05-11' and  t1.date < '2016-05-14'

edited May 12 '16 at 11:19

answered May 12 '16 at 10:28

Abdul Rasheed

6,486
4
32
48

@TomTom : What about this answer?? – Ullas May 12 '16 at 10:38
@Abdul Rasheed, casting query is working if I have Data Type "Date" in database....but not working with Data Type "datetime". – Vivek Gondliya May 12 '16 at 11:01
if you are not getting result for the query - where cast(t1.date as date) between '2016-05-11' and '2016-05-13' , then there should be no records in this period. – Abdul Rasheed May 12 '16 at 11:10
See the Image....in question...there is 3 records available between '2016-05-11' and '2016-05-13'...Query is running successfully but data fetch result is Null – Vivek Gondliya May 12 '16 at 11:13
then chk - where t1.date >= '2016-05-11' and t1.date < '2016-05-14'? Yes it is t1.date < '2016-05-14' – Abdul Rasheed May 12 '16 at 11:19
I know that but finding other alternative – Vivek Gondliya May 12 '16 at 11:31
And what about IN query...which is Query 2 – Vivek Gondliya May 12 '16 at 11:33
IN query will not work for u , bcs it is exact match (your time fraction also will be same then only u wll get the result). r u getting the expected result in >= and < query? – Abdul Rasheed May 12 '16 at 11:38

Not able to fetch records by Date from database

2 Answers2