VB.NET and SQL datetime

[ringo747]

Hi, I have a datetime column in my SQL Server DB table an have some records with a value of NULL. I am trying to load an object with date values. Because the date is null my variable has a value of "12:00:00". How can I ensure that my Date variable is "" instead of the "12:00:00" which causes a SqlDateTime overflow exception. My variable is set up as a Nullable(Of Date) but I have just been having so many problems with Null dates in VB.NET. What is the best procedure for working with null dates?

 

[newm]

I usually declare them as objects so that you can check them for Null. If your using properties it might look like:

Private _DOB AS Object

Public Property DateOfBirth() As Object
Get
     Return _DOB
End Get
Set(ByVal value As Object)
     _DOB = value
End Set
End Property

As long as you make sure you only ever assign dates it should be ok.

 

[ringo747]

Yes I have them declared as a date type at the minute but maybe it should be nullable(of date) ?? I have code that looks exactly like yours except instead of object it is Date.

I think the problem lies in the fact that even though the DB contains NULL there is a time value appears in the Date object. I think on loading the object I will have to check for null dates but i don't think this is possible unless the date object is made nullable. Im newish to .net so not sure on this really.

 

[newm]

You could use Nullable e.g.

Private _DOB AS Nullable(Of Date)

Public Property DateOfBirth() As Nullable(Of Date)
Get
     Return _DOB
End Get
Set(ByVal value As Nullable(Of Date))
     _DOB = value
End Set
End Property

Then when you get your data check for Null e.g.,

If myDataTable.Rows(0)("DateOfBirth") Is DBNull.Value Then 
     _DOB = Nothing
Else
     _DOB = myDataTable.Rows(0)("DateOfBirth")
End If

 

[ringo747]

yeah I guess that could work well. I just don't understand why it currently shows just a time in the date field when in the db the value is null. Will have a look again at nullable objects.

I am right in thinking that if you want to allow null values for an object then you must use nullable in the property definition? Therefore if I don't want to allow nulls then I just define the object as type date?

 

[newm]

If you use the above code you can also use the HasValue property to determine if it is empty, see http://msdn.microsoft.com/en-us/library/sksw8094.aspx

I think its just dates that will you problems, I dont think there is any such thing as a Null date in .NET.

 

[ringo747]

thanks for the info. Am familiar with hasvalue but will have anotther look at the nullable.