MS Access import int Sql Server columns contain bad dates

I have an access db I'm trying to upsize into Sql Server. There are over 100 tables most of them contain date columns. Without knowing which table/column may contain a bad date is there anyway that I can create a global function that will be triggered when a date column is imported that will essentially reject the bad date and substitute with a null value?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Eric ShermanAccountant/DeveloperCommented:
Can you explain what a bad date looks like in your source tables?

bensonwalkerAuthor Commented:
9/1/204 is one example
Jeffrey CoachmanMIS LiasonCommented:
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.

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

bensonwalkerAuthor Commented:
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
Eric ShermanAccountant/DeveloperCommented:
Another approach is create a VBA Function to loop through each table and replace the bad dates with the null value.  


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bensonwalkerAuthor Commented:
Now that sounds like a possibility. Do you have a code sample that loops thru all tables / columns in an access db ?
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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.
bensonwalkerAuthor Commented:
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
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Still waiting on an actionable definition of 'bad date', but just for kicks and giggles...


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


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


Copy-paste the resulting set of 'bad dates' into this question.
Gustav BrockCIOCommented:
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.

Jeffrey CoachmanMIS LiasonCommented:
...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:
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:
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)

Eric ShermanAccountant/DeveloperCommented:
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.

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.)

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.
bensonwalkerAuthor Commented:
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.
bensonwalkerAuthor Commented:
' 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
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

   Set db = Nothing
   Exit Function

   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"
                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"
                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"
                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
        If rs.Fields(0) <= #1/1/2004# Or rs.Fields(0) >= #1/1/2020# Then
        rs.Fields(0) = Null
        End If
        'Move to the next record. Don't ever forget to do this.
    End If
End Sub
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.