Insert Error..Data Overflow 1

[millzy7]

Hi

I'm migrating from Access 2000 to SQL Server 2000 and I cant import 2 specific tables. I keep getting the following error:


"Insert Error, column 7('EndTime',DBTYPE_DBTIMESTAMP),status 6: Data Overflow"

Any ideas?

Thanks

 

[JoeSack]

Hi,

You may have dates in the Access database that are out of range for SQL Server. Access supports dates as far back as Jan 1, 100, while SQL Server only supports dates back to Jan 1, 1753. Check the Access data for bad entries (missing chunks of the date).


Best Regards,
Joseph Sack,

http://www.JoeSack.com

Author of "SQL Server 2000 Fast Answers for DBAs and Developers".

 

[millzy7]

Thanks

I think the problem is that field contained times in the format 06:00:00 . Does SQL Server have a problem with this or is there a specific data type just for times?

 

[JoeSack]

It shouldn't have a problem with that time format, no. Datetime should be able to store the full date and time.

Best Regards,
Joseph Sack,

http://www.JoeSack.com

Author of "SQL Server 2000 Fast Answers for DBAs and Developers".

 

[dbomrrsm]

If you only want the date and can live without the time create the following UDF and use it when importing the data.

It will give you just the date from the date/time field.

CREATE FUNCTION justdate (@dttm DATETIME)
RETURNS nvarchar(10)
AS


BEGIN
DECLARE @udfddmmyyyy nvarchar(10)
SELECT @udfddmmyyyy = RIGHT('0' + CAST(Day(@dttm) AS
Varchar(2)), 2) + '/' + RIGHT('0' + CAST(Month(@dttm) AS
Varchar(2)), 2) + '/' + CAST(Year(@dttm) AS Varchar(4))
RETURN(@udfddmmyyyy)
END


Example
--SELECT dbo.justdate(getdate())

 

[coolaid808]

Hello Millzy,

I had the same problem with valid dates (2099-12-31). The Import Export Wizard in SQL Server automatically creates a table when the table does not exist when importing.
In Access (my source) the datetime field is created in SQL Server (my destination) as a SmallDatetime field.

SmallDatetime: Date and time data from January 1, 1900, through June 6, 2079
Datetime: Date and time data from January 1, 1753 through December 31, 9999

So I changed the definition in SQL Server from SmallDatetime to DateTime and the import succeeds.

Regards, Roel - Yellow & Red

 

[coolaid808]

What I mean with:

> So I changed the definition in SQL Server from SmallDatetime to DateTime and the import succeeds.

Is that I changed the datatype of the 'EndDate' Column in the table design of the table where the import fails (by means of the SQL Enterprise Mgr). You can also use the ALTER TABLE statement in transact sql to do so.

regards.