Why date comparison in sql is not working. Please help

Question

I am trying to fetch some records from table but when i use OR instead of AND it returns me few records but not in other case. dates given exactly are present in table. What mistake i am doing ?

select newsid,title,detail,hotnews 
from view_newsmaster
where  datefrom>=CONVERT(datetime, '4-22-2010',111) 
  AND dateto<=CONVERT(datetime, '4-22-2010',111)

Answer 1

When you are using AND, you will get dates between the two dates (in this case, the same date).

When you are using OR you will get dates larger than the first and lower then the second.

They are two different conditions, so it stands to reason that your results will be different.

What are you trying to achieve? What should this query return?

Answer 2

I think you have your "from" and "to" round the wrong way...

select newsid,title,detail,hotnews from view_newsmaster 
 where  dateto>=CONVERT(datetime, '4-22-2010',111) AND datefrom<=CONVERT(datetime, '4-22-2010',111) 

Answer 3

Hard to say without any exemplary data from your table.

I would check how many records have datefrom value greater then dateto value -- it looks strange for me -- this could be a reason why query with AND doesn't return anything.

Try to replace dateto value with CONVERT(datetime, '4-23-2010',111) -- keep it mind that CONVERT(datetime, '4-22-2010',111) is midnight (beginning of day) 22ed April, not the end of day.

Answer 4

It's because dates imply time 00:00:00, and not specifying times will exclude those records falling on a day, but are technically within range.

Specify times:

where  datefrom>=CONVERT(datetime, '4-22-2010 00:00:00',111) 
  AND dateto<=CONVERT(datetime, '4-22-2010 23:59:59',111)

Or better yet, use the BETWEEN operator :

where  fDateField BETWEEN '4-22-2010' AND '4-23-2010'

Note how the second date is date+1, ie the next day, which is basically date 23:59:59