Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    A2K/SQL Server (A2K/SQL 2K)

    I have an issue for the SQL Server experts out there. I have imported a table to SQL Server 2000 from Access 2K. I have three time only date fields that I use. SQL Server will not import these fields without first converting them to "Text" in Access. I get this error: "Invalid character type for cast specification, data overflow". I have checked all of the dates to make sure that they are not out of range. Also, I have tried to convert the dates to different formats in Access but that has no effect.

    Thanks,
    Mark

  2. #2
    Star Lounger
    Join Date
    Mar 2001
    Location
    Dudley, Midlands, England
    Posts
    55
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: A2K/SQL Server (A2K/SQL 2K)

    The problem is caused by SQL converting Access' DateTime field to a smalldatetime field, I think, rather than a datetime field. The smalldatetime only allows dates in the range January 1, 1900, to June 6, 2079 and an Access DateTime field with just a time in is dated 30th December 1899.

    I don't know how to resolve this from within SQL. If the import is a one-off, exporting the data from Access should work OK - you should find that SQL server has already created you a table (albeit with smalldatetime fields where you want datetime) so you could use that table as a linked table. An alternative might be to use Access' upsizing wizard.

    Simon.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: A2K/SQL Server (A2K/SQL 2K)

    How did you import it? Using DTS you should be able to instruct the engine to convert the date to datetime.
    Charlotte

Posting Permissions

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