VB Dates and DBNull.Value SQL

One think I find that I have to look up often is how to work with database dates in vb.net code. I can never remember the best way to do it and end up looking at older code all the time to do it.

Dates are not usually the problem as much as empty dates. Moving an empty date from vb to SQL and back again. I don't expect my solution to be the best solutions. Personally I feel its too complicated so there had to be a better solution that I just have not found yet. With that said, here is how I handle null dates in vb.net.

I don't like working with null values when crossing between applications and code. Mostly because a blank value can serve the same purpose with less code. Dates are the one place I can't use a blank value and it must be null. I will show the use of both strings and dates so you can easily see how they differ.

Here is how I grab values from a DataRow object called row:

dim name as string = row("Name").ToString()
dim DOB as Nullable(Of DateTime) = row.Field(Of Nullable(Of DateTime))("DOB")

My use of ToString() is a quick way to convert DBNull strings into a empty string. I could store my date as a string and do the same thing, but there are times I want to keep it as a datetime object. I could use a plain DateTime and just check the value as I read it for DBNull and adjust to that. Or I can use a Nullable DateTime object to do the same thing for me.

When I am saving the values, I do check for values when I add them as Parameters. Here is how that code looks like:


Dim command As New SqlClient.SqlCommand()

command.Parameters.AddWithValue("@Name", Name)
If DOB Is Nothing Then
command.Parameters.AddWithValue("@DOB", DBNull.Value)
Else
command.Parameters.AddWithValue("@DOB", DOB)
End If

One other thing that you need to watch is using DBNull values in a where statement. By default NULL=NULL is false. You have to check for IS NULL instead. If you want more details on why that is look up ANSI_NULLS. I mention this because its very common to want to use a parameter in a WHERE statement. If you don't craft it correctly, you will not get the results you expect when the value is NULL.

In your mind you expect this to work:

SELECT * FROM people WHERE DOB = @DOB

You want to use a DBNULL to find all the people without a DOB but it will return 0 records. I bet you could mess with the ANSI_NULL option or change the query to this:

SELECT * FROM people WHERE (DOB = @DOB OR (@DOB IS NULL AND DOB IS NULL))

This will do the normal check on the param, but will also check to see if both are null. I know I am doing a lot of extra work someplace, but this does work for me. Turning off the ANSI_NULL option will make the first query work, but I have not looked into it enough to know if it has any negative effects.