MS Access import int Sql Server columns contain bad dates

Can you explain what a bad date looks like in your source tables?

ET

9/1/204 is one example

You could use the ISDate() function
I really hate "Replacing" values as this will destroy any history, ...if a mistake was made in the system, or the conversion.
Create a query like this perhaps:
SELECT f1, f2, YourDateField, IIF(IsDate([YourDateField])=False,Null,[YourDateField]) AS TestDate
FROM YourTable

Then import this query...
Once you validate the dates in SQL Server, ...then you can "delete" as you see fit.

JeffCoachman

That means I have to write a query for every table that has an issue. I was hoping there was a way to use the Sql Import Utility to trap an offending date column from any table and substitute null

Now that sounds like a possibility. Do you have a code sample that loops thru all tables / columns in an access db ?

Define 'bad date'.  February 31st?  January 1, 1900?  'banana'?  May 10, 2015 which is a Sunday and our offices are closed?  Any date before our contract was signed?  Fat and ugly?  Kept talking about her ex-boyfriend, conversion to a new religion such as the Church of Satanic Rights, or was unimpressed when you told her what car you drive?

I have code to handle a time series and multiple definitions of 'bad date' such as gaps, overlays, and duplicates in this article called T-SQL: Identify bad dates in a time series.  Knock yourself out, and hit the big green 'Was this article helpful?' button if you liked it.

>There are over 100 (Access) tables most of them contain date columns.
>import int Sql Server
Curiosity overwhelms me as to what values would be accepted in an Access Date/Time column, but would not be accepted in a SQL Server datetime column.

Problem is the tables wont convert to Sql because of the bad date in the column. I would think I need to fix first than import

Still waiting on an actionable definition of 'bad date', but just for kicks and giggles...

1.

Import your Access table into SQL Server, specifically importing the 'date' column into a SQL varchar() column.

2.

Run a SQL Select query using Jeffy's ISDATE() recommendation, like this, which will display any column that SQL cannot convert to a date format...

SELECT whatever_date_field
FROM your_new_table
WHERE ISDATE(whatever_date_field) = 0

3.

Copy-paste the resulting set of 'bad dates' into this question.

Jim is right. 9/1/204 is a valid date unlike, say, 2002-02-30.

But it is not a likely date for a business. Thus you will have to define your rules for validation - including what to do with unlikely dates - replace by Null, taken out for manual inspection, given an average value from similar records, or what?

Sanitizing is not easy and the shortcuts are few if any.

/gustav

Yep
1/1/97
5/89
...can also be valid dates in some systems.

Are leading zeros in months or days acceptable? (07/09/2015, and what about 07/9/2015)
Is the full 4 digit year required, ...if not, then lets not forget and the infamous Y2K considerations:
 6/12/20
is this to be considered 6/12/1920 or 6/12/2020?
...and what is the cutoff year?
 (MS uses 1920, ...I think, but I have seen some apps using 1950...)

 What about leap years (Feb 29th)

And what about international dates:
dd/mm/yyyy
Here 29/07/2015 is a valid date, ...but not in the US format (mm/dd/yyyy)

What about dates in the ISO International date format:
DD-MM-YYYY (have dashes instead of slashes)

JeffCoachman

bensonwalker you said ...

Now that sounds like a possibility. Do you have a code sample that loops thru all tables / columns in an access db

To quote Gustav Brock ...

Sanitizing is not easy and the shortcuts are few if any.

Any solution you come up with based on your request will involve a bit of work to be honest.

ET

Jim,
the SQL Server datetime data type uses a different range than the Access data type.  THAT is the invalid date problem.

The valid range for datetime in SQL Server is January 1, 1753, through December 31, 9999

For Access, the valid values are:
Valid date values range from -657,434 (January 1, 100 A.D.) to 2,958,465 (December 31, 9999 A.D.)

bensonwalker,
If you are using utilities to transfer data such as the upsizing wizards, you are SOL unless you fix the dates ahead of time.  I think the Access upsizing wizard is more forgiving and may simply ignore the out of range values but the SSMA stops dead.

I ran through the conversion and for each of the tables that had errors, I opened them in DS view and sorted ascending and then descending on all the data columns.  Of course, I only had 4 tables with issues and a total of 7 potential columns.  So, depending on the scope of your problem, you may want to create some automation.  If you are going to do that, I would create a log of the errors with the record ID and the bad value so the users could fix them.

This fixed the issue for me. I determined the beginning date that our customer started using the system. Built a VBA Loop that worked thru the tables identifying the fields that were defined as Date/Time. When I found such a field I looped thru the rows of the table checking to see if the feilds value fell between begging date and the current date and if it didn't I just set it to null. May not be perfect but it got me past this mess.

Great solution.  Since the routine sounds pretty generic, perhaps you'd like to share your code.

' cobbled together from a few snippets found out on the web

Option Compare Database
Public Sub FixDates()

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Set db = CurrentDb
For Each tdf In db.TableDefs
    ' ignore system and temporary tables
    If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*") Then
        TableInfo (tdf.Name)
        Debug.Print tdf.Name
    End If
Next
Debug.Print "end"
Set tdf = Nothing
Set db = Nothing
End Sub
Function TableInfo(strTableName As String)
On Error GoTo TableInfoErr
   ' Purpose:   Display the field names, types, sizes and descriptions for a table.
   ' Argument:  Name of a table in the current database.
   Dim db As DAO.Database
   Dim tdf As DAO.TableDef
   Dim fld As DAO.Field
   
   Set db = CurrentDb()
   Set tdf = db.TableDefs(strTableName)

   For Each fld In tdf.Fields
        Select Case FieldTypeName(fld)
      Case "Date/Time", "Time"
      CorrectDateFormat fld.Name, strTableName
      End Select
   Next

TableInfoExit:
   Set db = Nothing
   Exit Function

TableInfoErr:
   Select Case Err
   Case 3265&  'Table name invalid
      MsgBox strTableName & " table doesn't exist"
   Case Else
      Debug.Print "TableInfo() Error " & Err & ": " & Error
   End Select
   Resume TableInfoExit
End Function


Function GetDescrip(obj As Object) As String
    On Error Resume Next
    GetDescrip = obj.Properties("Description")
End Function


Function FieldTypeName(fld As DAO.Field) As String
    'Purpose: Converts the numeric results of DAO Field.Type to text.
    Dim strReturn As String    'Name to return

    Select Case CLng(fld.Type) 'fld.Type is Integer, but constants are Long.
        Case dbBoolean: strReturn = "Yes/No"            ' 1
        Case dbByte: strReturn = "Byte"                 ' 2
        Case dbInteger: strReturn = "Integer"           ' 3
        Case dbLong                                     ' 4
            If (fld.Attributes And dbAutoIncrField) = 0& Then
                strReturn = "Long Integer"
            Else
                strReturn = "AutoNumber"
            End If
        Case dbCurrency: strReturn = "Currency"         ' 5
        Case dbSingle: strReturn = "Single"             ' 6
        Case dbDouble: strReturn = "Double"             ' 7
        Case dbDate: strReturn = "Date/Time"            ' 8
        Case dbBinary: strReturn = "Binary"             ' 9 (no interface)
        Case dbText                                     '10
            If (fld.Attributes And dbFixedField) = 0& Then
                strReturn = "Text"
            Else
                strReturn = "Text (fixed width)"        '(no interface)
            End If
        Case dbLongBinary: strReturn = "OLE Object"     '11
        Case dbMemo                                     '12
            If (fld.Attributes And dbHyperlinkField) = 0& Then
                strReturn = "Memo"
            Else
                strReturn = "Hyperlink"
            End If
        Case dbGUID: strReturn = "GUID"                 '15

        'Attached tables only: cannot create these in JET.
        Case dbBigInt: strReturn = "Big Integer"        '16
        Case dbVarBinary: strReturn = "VarBinary"       '17
        Case dbChar: strReturn = "Char"                 '18
        Case dbNumeric: strReturn = "Numeric"           '19
        Case dbDecimal: strReturn = "Decimal"           '20
        Case dbFloat: strReturn = "Float"               '21
        Case dbTime: strReturn = "Time"                 '22
        Case dbTimeStamp: strReturn = "Time Stamp"      '23

        'Constants for complex types don't work prior to Access 2007 and later.
        Case 101&: strReturn = "Attachment"         'dbAttachment
        Case 102&: strReturn = "Complex Byte"       'dbComplexByte
        Case 103&: strReturn = "Complex Integer"    'dbComplexInteger
        Case 104&: strReturn = "Complex Long"       'dbComplexLong
        Case 105&: strReturn = "Complex Single"     'dbComplexSingle
        Case 106&: strReturn = "Complex Double"     'dbComplexDouble
        Case 107&: strReturn = "Complex GUID"       'dbComplexGUID
        Case 108&: strReturn = "Complex Decimal"    'dbComplexDecimal
        Case 109&: strReturn = "Complex Text"       'dbComplexText
        Case Else: strReturn = "Field type " & fld.Type & " unknown"
    End Select

    FieldTypeName = strReturn
End Function
Sub CorrectDateFormat(strField, strTable)
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT " & strField & " FROM " & strTable)
'Check to see if the recordset actually contains rows
If Not (rs.EOF And rs.BOF) Then
    rs.MoveFirst 'Unnecessary in this case, but still a good habit
    Do Until rs.EOF = True
        'Perform an edit
        rs.Edit
        If rs.Fields(0) <= #1/1/2004# Or rs.Fields(0) >= #1/1/2020# Then
        rs.Fields(0) = Null
        End If
        rs.Update
        'Move to the next record. Don't ever forget to do this.
        rs.MoveNext
    Loop
    End If
   
End Sub