CODEWITHSUNDEEP
sql-serverdatetimetypessql
Shantanu Gupta
Shantanu Gupta

Reputation: 21188

Why date comparison in sql is not working. Please help

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)

Upvotes: 3

Views: 7494

Answers (4)

invert
invert

Reputation: 2076

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

Upvotes: 1

Grzegorz Gierlik
Grzegorz Gierlik

Reputation: 11232

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.

Upvotes: 2

Robin Day
Robin Day

Reputation: 102458

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)

Upvotes: 2

Oded
Oded

Reputation: 498904

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?

Upvotes: 0

Related Questions