CODEWITHSUNDEEP
asp.netsqlsql-servervb.nett-sql
HShbib
HShbib

Reputation: 1841

SQL: Not equal operator Problem

I am using a not equal operator <> in my sql statement but it doesn't retrieve any record which is not equal to the selected date.

CODE:

 Command = New SqlCommand("SELECT * FROM [Products] WHERE [ParkingStartDate] <> @StartDate", myConn)

 Command.Parameters.AddWithValue("@StartDate", StartDate1)

Upvotes: 3

Views: 7687

Answers (6)

jitendrapurohit
jitendrapurohit

Reputation: 9675

My recommendation would be to try with NULLIF operator. Modify your query to be like :

SELECT * FROM [Products] WHERE NULLIF([ParkingStartDate], @StartDate) IS NOT NULL  OR ParkingStartDate is NULL

Hope this helps.

Upvotes: 0

Mandeep Singh
Mandeep Singh

Reputation: 1003

If the value is undefined, it is not included in <> or != clause. Along with these you can use sql function 'COALESCE()' to include rows having undefined cells.

   "SELECT * FROM [Products] WHERE COALESCE([ParkingStartDate],'') <> @StartDate OR ParkingStartDate is null"

Hope it will help you.

Upvotes: 0

cdeszaq
cdeszaq

Reputation: 31300

One important thing to take into consideration when dealing with querying based on date is that the date in SQL Server is treated as exact as the date you send in. So, if you pass in a full date/time, like 2011-10-24 14:35:29, it will return all dates that are not that exact date. If you are looking for a particular portion of that date to be selected against, you need to only give that portion of the date. Using the DATEPART command will help here also.

Upvotes: 0

Doug Chamberlain
Doug Chamberlain

Reputation: 11351

First stop using that <> operator.

Use instead != (NOT EQUAL)

run this statement in sql. it will return zero results. to illustrate my point.

select '1' where NULL <> 0

instead use

where columname != @startdate or columnname is null

Upvotes: 0

Wegged
Wegged

Reputation: 2423

If the values are null you would have to do 

Command = New SqlCommand("SELECT * FROM [Products] WHERE [ParkingStartDate] <> @StartDate OR ParkingStartDate is null", myConn)

 Command.Parameters.AddWithValue("@StartDate", StartDate1)

Upvotes: 2

Quassnoi
Quassnoi

Reputation: 425733

This won't return anything if either of the following is true:

  • StartDate1 is a NULL
  • ParkingStartDate for all values is a NULL or equal to StartDate1 (obvious one)

Check that you are passing a non-NULL value in StartDate1 and there are records satisfying your condition.

Upvotes: 3

Related Questions