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.Add开发者_StackOverflowWithValue("@StartDate", StartDate1)

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.

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)

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

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.

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.

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.