Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2003
    Posts
    11

    Unanswered: Conversion errors on date field from Access to SQL server7

    Hi
    1st time trying to migrate Access 2000 tables to SQLServer7.
    The tables transport but I'm getting errors on the data transfer.

    The error is based on the date/time field in Access...ex: DOB (DateofBirth) field is formatted as shortdate.

    When the error occurs in transport it reads:
    Error at Destination for Row number 310...
    Insert error, column 16('DOB', DBTYPE_DBTIMESTAMP), status 6. Data overflow. Invalid character value for cast specification.

    **What I have found so far is that this error occurs on the rows in the DOB field where the year of birth is before 1900 (ie:1897)...or in some instances if the year is mistakenly in as...example: 9194 (as opposed to 1994) it will not except the transfer.

    I have created a mock table with a date/time field of this format (with all the years being in 2002) and it transfers fine!

    Any ideas on how I get the SQL Server to accept these records??

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    use datetime rather than smalldatetime.

    valid datetime range is 1-Jan-1753 to 31-Dec-9999 23:59:59.9999
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Jan 2003
    Posts
    11

    Thanks for the reply but...

    I did convert the SQL field to datetime...but it still gives conversion errors on date fields that are in the 1800's!!??!!

    If I change those to 01/01/1900...they will transfer.

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    I must be missing the point.... if the date is '01-Jan-1897' it would go into a datetime field with out problems. can you provide an example of a trouble maker?
    Paul Young
    (Knowledge is power! Get some!)

  5. #5
    Join Date
    Jan 2003
    Posts
    11

    Sorry for any confusion...

    Here goes...there were some records that had dates like this:
    01/01/9194
    01/01/1897
    01/01/1583 etc....

    You had mentioned that if I changed the SQL field to datetime from smalldatetime (which I had already done)...then it would tranfer data from 1-Jan-1753 to 31-Dec-9999

    Well...after I changed the field to datetime the records, such as, 01/01/1897 wouldn't transfer...even though they were in the valid range for datetime.

    [And if I changed all the records that were before the year 1900 to a date after 1900 it would transfer].

    Hope this clears it up.

  6. #6
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    thanks!

    You may have other problems here, consider the following code:

    Code:
    declare @dt datetime, @vc varchar(100)
    set @dt = '01/01/9194'
    set @vc = cast(@dt as varchar)
    select @dt, @vc
    set @dt = '01/01/1897'
    set @vc = cast(@dt as varchar)
    select @dt, @vc
    set @dt = '01/01/1583'
    set @vc = cast(@dt as varchar)
    select @dt, @vc
    As one would expect the last date is a problem. Could you import the date data into a varchar field and then selectivly convert the data?
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •