joe joe - 4 months ago 10
SQL Question

vb.net Date values being converted to time

I have the following code to check whether or not a datetime value is null or has a value. If it's null, I want it to be returned as a blank string, else it should be a string of the value it contains.

The

Else
part of the statement works, it converts the value into a string, however, if the value being passed in as a datetime is null, it doesn't return
Nothing
, it instead sets the value to '12:00:00 AM`, which is causing me problems elsewhere in the project.

How can I adapt this function to make null datetime values return as blank strings?

Public Shared Function dbToDate(o As Object) As DateTime

If o Is DBNull.Value Then
Return ""

Else
Return Convert.ToDateTime(o)

End If
End Function

Answer

First, a DateTime is never null/nothing. Since it's a value type(Structure) it will always have a value. In this case the default value is DateTime.MinValue. But VB.NET handles this case for you. The dt.Date = Nothing will automatically compare the value with Date.MinValue so it's working as desired.

But the problem is that you return dt.ToString. Just return "":

Public Shared Function sqlDate(dt As DateTime) As String    
    If dt.Date = Nothing Then  
        Return ""    
    Else    
        Return dt.ToString("yyyy-MM-dd")    
    End If    
End Function

Here's a version without method:

Dim result = If(dt = Nothing, "", dt.ToString("yyyy-MM-dd"))

You can see that a Date is never null with this code:

Dim dt As Date = Nothing
Console.WriteLine( dt = Nothing )' true
Console.WriteLine( dt = Date.MinValue )' true
Console.WriteLine( Object.ReferenceEquals(Nothing, dt) )' always false because it's a value type

Update acc. your latest edit, compare with DbNull.Value. I'd also return a Nullable(Of Date):

Public Shared Function dbToDate(o As Object) As DateTime?
    If DbNull.Value.Equals(o) OrElse o Is Nothing Then
        Return Nothing
    Else
        Return Convert.ToDateTime(o)
    End If
End Function