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.Globaliz ation.CultureIn fo("", True)
Private Function ConvertDataObje ct(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.DataTyp e Is GetType(System. String)) Then
'Compensate for aposterphes
Obj = Obj.Replace("'" , "''")

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

If Obj = " " Or Obj = "" Or Obj = " " Then
Obj = ""
Else
Obj = DateTime.ParseE xact(Obj, dtarray, culture,
Globalization.D ateTimeStyles.N one)
End If
Catch ex As Exception
If Not (fileLogger Is Nothing) Then
'HasGlobalError = True
fileLogger.LogD irectEntry("War ning: An invalid date/time
field of " & Obj & " was identified on line #" & currLineNo.ToSt ring() & ",
column " & Column.ColumnNa me & ", 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.Globaliz ation.CultureIn fo("", True)
Private Function ConvertDataObje ct(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.DataTyp e Is GetType(System. String)) Then
'Compensate for aposterphes
Obj = Obj.Replace("'" , "''")

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

If Obj = " " Or Obj = "" Or Obj = " " Then
Obj = ""
Else
Obj = DateTime.ParseE xact(Obj, dtarray, culture,
Globalization.D ateTimeStyles.N one)
End If
Catch ex As Exception
If Not (fileLogger Is Nothing) Then
'HasGlobalError = True
fileLogger.LogD irectEntry("War ning: An invalid date/time
field of " & Obj & " was identified on line #" & currLineNo.ToSt ring() & ",
column " & Column.ColumnNa me & ", 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.Globaliz ation.CultureIn fo("", True)
Private Function ConvertDataObje ct(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.DataTyp e Is GetType(System. String)) Then
'Compensate for aposterphes
Obj = Obj.Replace("'" , "''")

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

If Obj = " " Or Obj = "" Or Obj = " " Then
Obj = ""
Else
Obj = DateTime.ParseE xact(Obj, dtarray, culture,
Globalization.D ateTimeStyles.N one)
End If
Catch ex As Exception
If Not (fileLogger Is Nothing) Then
'HasGlobalError = True
fileLogger.LogD irectEntry("War ning: An invalid date/time
field of " & Obj & " was identified on line #" & currLineNo.ToSt ring() & ",
column " & Column.ColumnNa me & ", 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