Dataview filter for null values in a date column

[donnacha]

Hi folks,

I am trying to create a dataview where I want to filter the rows based on a date column containing Nulls.

 

No all the examples on filtering for null give

"Isnull(Col1,'Null Column') = 'Null Column'"

 

Now when I replace Col1 with my column name for my date column

"Isnull(Postal_Date,'Null Column') = 'Null Column'"

 

I get the following error

"Cannot perform '=' operation on System.DateTime and System.String."

 

Has anybody solved this problem or am I doing it all wrong.

 

:confused: :confused:

[donnacha]

I have change the filter to use

"Postal_Date IS NULL"

and that works. Maybe I had the wrong end of the stick altogether with the IsNull method, cany anybody explain the difference.

[bri189a]

IsNull() is a function that replaces a null value with a specified value. In other words if there where any null values in the column "UserName" the following would update those values to "No User Name" for the return of the select statement (it doesn't update the dataset itself):

 

"SELECT ISNULL(UserName, 'No User Name'), JoinDate FROM Members"

 

'IS NULL' is a comparission operator for null values, which you want to use.

[donnacha]

The reason why I used the IsNull in the first place is that the online help for Dataview.RowFilter says

 

"To return only those columns with null values, use the following expression:

 

"Isnull(Col1,'Null Column') = 'Null Column'"

 

I tried it on non date comumns and it seemed to work OK

 

Is it a pecularity with date columns (see error message in origional post.)

 

The Is Null is working for me I am hust trying to understand why there is a difference between what works and ehat the online help says should work.

(Then again it is Microsoft, so maybe there is no reason)

[bri189a]

That expression in the help file is saying in plain english to take all null columns and set there value to the string 'Null Column' and then return any rows where Col1 equals the string 'Null Column'. .NET cannot take a DateTime field and set it to a string value on it's own (there is no implicit cast) and thus the reason for the error. It is in truth a bad example in the help file and should read:

 

To return only those columns with null values, use the following expression:

"Col1 IS NULL"

 

I hope that clears things up for you.