Year, Month, and Day parameters describe an un-representable DateTime how to ignore

Question

I am using ODBC to read data from a pervasive PSQL database and in some scenarios, the date column can contain 00/00/0000 date. I don't really care about invalid dates so is there some way I can convert all of these un-representable dates into Null or some specific date instead of the query failing.

EDIT:

The following shows the code I am using and where it is failing:

Private _connODBC As OdbcConnection

Dim dt As New DataTable
_connODBC = New OdbcConnection(txtConnectionString.Text)
_connODBC.Open()

Dim dataadapter = New OdbcDataAdapter(QueryString, _connODBC)
dataadapter.Fill(dt) '<--- This line throws the unrepresentable date time error
_connODBC.Close()

Ideally we need a solution that doesn't require changing the SQL query. But rather intercept the error as it happens on the line `dataadapter.Fill(dt)` — CrazyTim, Apr 11 '13 at 00:34
@CrazyTim - I agree but no one has come up with that solution...yet — Matt Wilko, Apr 11 '13 at 08:06

score 2 · Answer 1 · answered May 24 '11 at 04:42

I've had this exact same problem. It was a while ago and I forget the exact details, but the problem is that the Pervasive.SQL uses &H0 (ie. zero) to represent a missing date, but &H0 is invalid for a date field in ODBC.

My solution was to cast the field to a string in the SQL, so use this in your QueryString: CONVERT(datefield,SQL_CHAR)

I then wrote the following function to convert it back to a date (&H0 becomes a New Date):

 ''' <summary>
''' Enables a Btrieve Date column that contains 0x0 values to be accessed. Use 'SELECT CONVERT(datefield, SQL_CHAR)' to access the field, then this function to convert the result back to a date. A New Date is returned if the record has a 0x0 date
''' </summary>
''' <param name="Expression"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Function SQL_CHAR2Date(ByVal Expression As String) As Date
    'check Expression is in the correct format
    Dim strMap As String = ""
    For i As Integer = 0 To Expression.Length - 1
        Select Case Expression.Substring(i, 1)
            Case "0" To "9" : strMap &= "0"
            Case "-" : strMap &= "-"
            Case Else : strMap &= Expression.Substring(i, 1)
        End Select
    Next i
    Select Case strMap
        Case "0000-00-00"
        Case Else
            Throw New ApplicationException("SQL_CHAR2Date: invalid input parameter")
    End Select

    Dim y As Integer = CInt(Expression.Substring(0, 4))
    Dim m As Integer = CInt(Expression.Substring(5, 2))
    Dim d As Integer = CInt(Expression.Substring(8, 2))

    If y = 0 And m = 0 And d = 0 Then
        Return New Date
    Else
        Return DateSerial(y, m, d)
    End If
End Function

Ooh, it was a long time ago... I think ArgumentException has been deprecated... change that to InvalidOperationException I suppose. — SSS, May 24 '11 at 04:45

score 1 · Answer 2 · answered May 20 '11 at 15:57

1

You can use a nullable DateTime - if the value is invalid set it to null (Nothing), otherwise to the date.

MyDate as Nullable(Of DateTime)

answered May 20 '11 at 15:57

Oded

489,969
99
883
1,009

My problem is how to catch the error to ascertain that it is invalid - please see my code sample added to the initial question – Matt Wilko May 23 '11 at 10:05

score 1 · Accepted Answer · answered May 24 '11 at 08:19

@SSS got me me thinking that this could be done in the query, although it is a bit long winded:

SELECT CASE CONVERT([updated date], SQL_CHAR) 
WHEN '0000-00-00' THEN NULL 
ELSE [updated date] END 
AS [updated date] FROM fingerscans

Although I would like a solution doesn't rely on me having to remember to do this whenever I read the database!

score 0 · Answer 4 · answered May 20 '11 at 16:31

0

Indirectly, if you import it into a text field instead of date, then, in a later step, you can identify and modify the text value however you want.

answered May 20 '11 at 16:31

Beth

9,531
1
24
43

Year, Month, and Day parameters describe an un-representable DateTime how to ignore

4 Answers4