Handling dates in VB.NET

[Yog]

Is there a best way to handle various formats of dates for SQL server?.

The data comes in various input files with different date formats.

The ParseExact function looks like an "evil" and it doesn't seem to work for
some cases.
'Date Time Stuff
Dim dtarray As String() = _
{"yyyyMMdd", "yyyy-MM-dd", "yyyy/MM/dd", _
"yyyyMd", "yyyy-M-d", "yyyy/M/d", _
"MMddyyyy", "MM-dd-yyyy", "MM/dd/yyyy", _
"Mdyyyy", "M-d-yyyy", "M/d/yyyy", _
"yyMMdd", "yy-MM-dd", "yy/MM/dd", _
"yyMd", "yy-M-d", "yy/M/d", _
"MMddyy", "MM-dd-yy", "MM/dd/yy", _
"Mdyy", "M-d-yy", "M/d/yy", _
" ", " ", " ", "", " "}

Dim culture = New System.Globalization.CultureInfo("", True)
Private Function ConvertDataObject(ByVal Column As DataColumn, ByVal Obj As
String, ByVal currLineNo As Integer) As String
'This builds the data type from the destination database

If (Obj Is GetType(DBNull)) Then
Obj = "null"
End If

If (Column.DataType Is GetType(System.String)) Then
'Compensate for aposterphes
Obj = Obj.Replace("'", "''")

Return "'" & Obj & "'"
ElseIf (Column.DataType Is GetType(System.DateTime)) Then
'Catch faulty date/time data types
Try

If Obj = " " Or Obj = "" Or Obj = " " Then
Obj = ""
Else
Obj = DateTime.ParseExact(Obj, dtarray, culture,
Globalization.DateTimeStyles.None)
End If
Catch ex As Exception
If Not (fileLogger Is Nothing) Then
'HasGlobalError = True
fileLogger.LogDirectEntry("Warning: An invalid date/time
field of " & Obj & " was identified on line #" & currLineNo.ToString() & ",
column " & Column.ColumnName & ", position " & Column.Ordinal.ToString() & ".
The field was truncated to a null entry to allow the record to be processed.")
End If
Return "null"
End Try

Return "'" & Obj & "'"
Else
Return Obj

End If

[Santhi Maadhaven]

Yog, one solution for this is while returning from database itself format the
date in particular format (ddmmyyyy) and return to UI.

"Yog" wrote:

Is there a best way to handle various formats of dates for SQL server?.

The data comes in various input files with different date formats.

The ParseExact function looks like an "evil" and it doesn't seem to work for
some cases.
'Date Time Stuff
Dim dtarray As String() = _
{"yyyyMMdd", "yyyy-MM-dd", "yyyy/MM/dd", _
"yyyyMd", "yyyy-M-d", "yyyy/M/d", _
"MMddyyyy", "MM-dd-yyyy", "MM/dd/yyyy", _
"Mdyyyy", "M-d-yyyy", "M/d/yyyy", _
"yyMMdd", "yy-MM-dd", "yy/MM/dd", _
"yyMd", "yy-M-d", "yy/M/d", _
"MMddyy", "MM-dd-yy", "MM/dd/yy", _
"Mdyy", "M-d-yy", "M/d/yy", _
" ", " ", " ", "", " "}

Dim culture = New System.Globalization.CultureInfo("", True)
Private Function ConvertDataObject(ByVal Column As DataColumn, ByVal Obj As
String, ByVal currLineNo As Integer) As String
'This builds the data type from the destination database

If (Obj Is GetType(DBNull)) Then
Obj = "null"
End If

If (Column.DataType Is GetType(System.String)) Then
'Compensate for aposterphes
Obj = Obj.Replace("'", "''")

Return "'" & Obj & "'"
ElseIf (Column.DataType Is GetType(System.DateTime)) Then
'Catch faulty date/time data types
Try

If Obj = " " Or Obj = "" Or Obj = " " Then
Obj = ""
Else
Obj = DateTime.ParseExact(Obj, dtarray, culture,
Globalization.DateTimeStyles.None)
End If
Catch ex As Exception
If Not (fileLogger Is Nothing) Then
'HasGlobalError = True
fileLogger.LogDirectEntry("Warning: An invalid date/time
field of " & Obj & " was identified on line #" & currLineNo.ToString() & ",
column " & Column.ColumnName & ", position " & Column.Ordinal.ToString() & ".
The field was truncated to a null entry to allow the record to be processed.")
End If
Return "null"
End Try

Return "'" & Obj & "'"
Else
Return Obj

End If

[Yog]

Thanks for answering...i am looking at dates that comes in various files with
different formats...not from the database..

"Santhi Maadhaven" wrote:

Yog, one solution for this is while returning from database itself format the
date in particular format (ddmmyyyy) and return to UI.

"Yog" wrote:

Is there a best way to handle various formats of dates for SQL server?.

The data comes in various input files with different date formats.

The ParseExact function looks like an "evil" and it doesn't seem to work for
some cases.
'Date Time Stuff
Dim dtarray As String() = _
{"yyyyMMdd", "yyyy-MM-dd", "yyyy/MM/dd", _
"yyyyMd", "yyyy-M-d", "yyyy/M/d", _
"MMddyyyy", "MM-dd-yyyy", "MM/dd/yyyy", _
"Mdyyyy", "M-d-yyyy", "M/d/yyyy", _
"yyMMdd", "yy-MM-dd", "yy/MM/dd", _
"yyMd", "yy-M-d", "yy/M/d", _
"MMddyy", "MM-dd-yy", "MM/dd/yy", _
"Mdyy", "M-d-yy", "M/d/yy", _
" ", " ", " ", "", " "}

Dim culture = New System.Globalization.CultureInfo("", True)
Private Function ConvertDataObject(ByVal Column As DataColumn, ByVal Obj As
String, ByVal currLineNo As Integer) As String
'This builds the data type from the destination database

If (Obj Is GetType(DBNull)) Then
Obj = "null"
End If

If (Column.DataType Is GetType(System.String)) Then
'Compensate for aposterphes
Obj = Obj.Replace("'", "''")

Return "'" & Obj & "'"
ElseIf (Column.DataType Is GetType(System.DateTime)) Then
'Catch faulty date/time data types
Try

If Obj = " " Or Obj = "" Or Obj = " " Then
Obj = ""
Else
Obj = DateTime.ParseExact(Obj, dtarray, culture,
Globalization.DateTimeStyles.None)
End If
Catch ex As Exception
If Not (fileLogger Is Nothing) Then
'HasGlobalError = True
fileLogger.LogDirectEntry("Warning: An invalid date/time
field of " & Obj & " was identified on line #" & currLineNo.ToString() & ",
column " & Column.ColumnName & ", position " & Column.Ordinal.ToString() & ".
The field was truncated to a null entry to allow the record to be processed.")
End If
Return "null"
End Try

Return "'" & Obj & "'"
Else
Return Obj

End If