SQL Server: Data overfolw error

[Snappy2873]

Good morning,
i'm currently trying to migrate and Access db to a SQL 2k environment. Everything transfers except one table which consists of mostly text columns but has (2) date columns. I've tried changing the date format before I DTS it over but once I execute I get this error:
"status 6: Data overflow. Invalid character value for cast
specification"
I've used general, long and medium date formats in Access but cannot clear this hurdle.
The column will transfer if I change it to text in Access and the result is an "nvarchar" data type with a result like this: 06/22/2004

Does anyone have a good T-SQL script reccom to convert
"nvarchar" data type to a "datetime" or "smalldatetime" data type?
Is there a more efficient way to accomplish this?

Thanks,
snappy

 

[Catadmin]

So, you're bringing the data from Access to SQL with a DTS Import job? The Access table has a date column and the SQL datatype DateTime won't work correctly? Or are you using SQL smallDateTime?

Either way, it sounds to me like there is a bad character or simply bad data in this table.

Go into Access, look at your table rows and sort them by Date Ascending. Then scroll through and see if you can spot anything that has an extra space or a - instead of \ or a date that is completely the wrong format.

If you have any blank boxes, do a delete in those fields just to make sure they aren't harboring blank space characters instead of being NULL.

Let us know if that doesn't help.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[Snappy2873]

Hey Catadmin,
I checked all 6500 records and your fix was successful. A few records were fat-fingered with a format that was unrecognized. After fixing the problems, SQL accepted my DTS with the default smalldatetime datatype.
Thanks for your help!
snappy

 

[SQLBill]

It looks like SQL Server is mis-interpreting the date value.

Is 06/22/2004 - June 22, 2004 or is it the 6th day of some unknown month in 2004 (causing an overflow error).

I suggest setting the values in Access to YYYYMMDD. I've never used DTS yet, but you might see if there's an option to set how Dates are being inputted. For example, in a SQL Server script I would put SET DATEFORMAT MDY and SQL would know that 06/22/2004 is June 22, 2004.

-SQLBill

Posting advice: FAQ481-4875

 

[Catadmin]

Glad I could help. @=)



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???