date wise search

Pooja Chowdhury

10y1.4k

i write one query

select * from userinfo

WHERE
[LoadDate]>='2016/10/01' and [LoadDate]<='2016/10/03'

the result shows of the date

01/10/2016 to 02/10/2016

i cannot see the data of 03/10/2016

why?

11 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ananth G10y ago
try this
select * from userinfo where Convert(Datetime,loaddate,111)>= CONVERT(Datetime, '2016/10/01', 103) and Convert(Datetime,loaddate,111) <=CONVERT(Datetime, '2016/10/03', 103)
0
Prakash Kumar10y ago
1. select * from userinfo where Convert(varchar,loaddate,111)>='2016/10/01' and Convert(varchar,loaddate,111) <= '2016/10/03'
3. select * from userinfo where Convert(varchar,loaddate,111) between '2016/10/01' and '2016/10/03'
try this one
+1

Nitin Sontakke10y ago

Please try to understand how query is seen by SQL Server. It looks as follows:

WHERE [LoadDate]>='2016/10/01 00:00:00' and [LoadDate]<='2016/10/03 00:00:00'

If you notice, the load date in later part of query is just the beginning of the 3rd October. The entire day is not covered in this query. Instead if you give explicit query as follows, it works:

WHERE [LoadDate]>='2016/10/01 00:00:00' and [LoadDate]<='2016/10/03 23:59:59'

Hope it helps!