locked
Handling Null Dates in WebApi RRS feed

  • Question

  • User1122355199 posted

    Hello everyone and thanks for your help in advance.  I am having problems with a WebApi that returns several dates as columns.  Some of the columns may or may not contain null values in the date columns.  The associated class with the data has columns defined as:

    Public Property OrderTime() As DateTime
            Get
                Return _OrderTime
            End Get
            Set(value As DateTime)
                _OrderTime = value
            End Set
        End Property
    
        Public Property ResultsReceived() As DateTime
            Get
                Return _ResultsReceived
            End Get
            Set(value As DateTime)
                _ResultsReceived = value
            End Set
        End Property

    While looping through the records of the database to populate the records, the code looks like:

                If Not IsDBNull(Order("OrderTime")) Then
                    OrderTime = Order("OrderTime")
                End If
    
                If Not IsDBNull(Order("ResultsReceivedDate")) Then
                    ResultsReceivedDate = Order("ResultsReceivedDate")
                End If
    and the list return looks like:

                    DiagnosticOrderList.Add(New DiagnosticOrder With {.ID = ID,
                                                                    .OrderTime = OrderTime,
                                                                    .ResultsReceived = ResultsReceived})
    However, when a Null value is encountered in one of the columns, I receive the following:

    System.InvalidCastException: Conversion from string "" to type 'Date' is not valid
    I guess I could change the class reference to string, but that seems like a hack to handle the problems, but I am not sure how to resolve the problem.  Any help would be appreciated.



    Sunday, May 28, 2017 9:37 PM

Answers

  • User753101303 posted

    Hi,

    My understanding is that Order is a reader and that this column (OrderTime and/or ResultsReceiveDate) returns a string rather than really a datetime ? On which line exactly do you see this error? It means basically you try to assign an empty string to a Date(Time).

    Also check perhaps https://docs.microsoft.com/en-us/dotnet/visual-basic/programming-guide/language-features/data-types/nullable-value-types depending on how you want to deal with null values. Currently it will keep the default value which is 1/1/1. It can be more convenient to use nullable datetimes.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, May 29, 2017 11:56 AM
  • User1967761114 posted

    Hi kmcnet,

    According to your description , you could define the property like the following code:

    Public Class AAA
        Dim _ResultsReceivedDate As DateTime?
    
        Public Property ResultsReceivedDate() As DateTime?
            Get
               Return _ResultsReceivedDate
            End Get
            Set
               _ResultsReceivedDate = Value
            End Set
        End Property
    End Class

    Otherwise it will return 0000-01-01T00:00:00 by default.

    If you have any other questions, please feel free to contact me any time.

    Best Regards

    Even

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, June 3, 2017 8:05 AM

All replies

  • User1967761114 posted

    Hi kmcnet,

    According to the code which you provide ,I guess that the column type of OrderTime and ResultReceivedDate is nvarchar(not datetime) in database, and the value is an empty string ,so it occurred this exception.

    To resolve this issue,your code could modify like this:

    If (Not IsDBNull(Order("Time"))) And (Not String.IsNullOrEmpty(Order("Time"))) Then
        OrderTime = Order("OrderTime")
    End If
    
    If (Not IsDBNull(Order("ResultsReceivedDate"))) And (NOT String.IsNullOrEmpty(Order("ResultsReceivedDate"))) Then
        ResultsReceivedDate = Order("ResultsReceivedDate")
    End If
    

    If you have any other questions, please feel free to contact me any time.

    Best Regards

    Even

    Monday, May 29, 2017 7:45 AM
  • User753101303 posted

    Hi,

    My understanding is that Order is a reader and that this column (OrderTime and/or ResultsReceiveDate) returns a string rather than really a datetime ? On which line exactly do you see this error? It means basically you try to assign an empty string to a Date(Time).

    Also check perhaps https://docs.microsoft.com/en-us/dotnet/visual-basic/programming-guide/language-features/data-types/nullable-value-types depending on how you want to deal with null values. Currently it will keep the default value which is 1/1/1. It can be more convenient to use nullable datetimes.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, May 29, 2017 11:56 AM
  • User1122355199 posted

    Thanks for the responses.  After reviewing the datatype article and some tweaks to the code, I've moved passed the error message, but am not sure what to do next.  On null datetime values, the value returned is 0001-01-01T00:00:00.  I would think best practices would be to return null, however that does not seem to work.  The changed I made to the code are:

        Public Property ResultsReceivedDate() As Nullable(Of DateTime)
            Get
                Return _ResultsReceivedDate
            End Get
            Set(value As Nullable(Of DateTime))
                _ResultsReceivedDate = value
            End Set
        End Property

    and the code to set the property:

                If Not IsDBNull(DetailLine("ResultsReceivedDate")) Then
                    ResultsReceivedDate = DetailLine("ResultsReceivedDate")
                Else
                    ResultsReceivedDate = Nothing
                End If

    So I'm not sure what is the best way to return null datetime values for client consumption.

    Thursday, June 1, 2017 12:10 AM
  • User1879451342 posted

    I'm not exactly sure given your situation, but if use a string, then perhaps set to:

    String.Empty

    or

    ResultsReceivedDate = DateTime.MinValue.ToString();

    Thursday, June 1, 2017 8:29 PM
  • User753101303 posted

    Ah are you 100% sure this is when you have null values in your db or could it be that you already have 1/1/1 dates in your db ? (if using datetime2 and if you assigned previously an empty string, it will default to 1/1/1 on the SQL Server side).

    Thursday, June 1, 2017 8:51 PM
  • User1967761114 posted

    Hi kmcnet,

    According to your description , you could define the property like the following code:

    Public Class AAA
        Dim _ResultsReceivedDate As DateTime?
    
        Public Property ResultsReceivedDate() As DateTime?
            Get
               Return _ResultsReceivedDate
            End Get
            Set
               _ResultsReceivedDate = Value
            End Set
        End Property
    End Class

    Otherwise it will return 0000-01-01T00:00:00 by default.

    If you have any other questions, please feel free to contact me any time.

    Best Regards

    Even

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, June 3, 2017 8:05 AM
  • User1122355199 posted

    Sorry for the delay in getting back.  It worked perfectly.  Thanks for the help.

    Thursday, June 8, 2017 12:08 AM