SQL Server Performance Forum – Threads Archive

Datatype Conversion

Hi we imported some tables from informix to sql 2005. we got an error for some tables. the error we got is insert error, column 31(‘actshipdt’, DBTYPE_DBTIMESTAMP), status 6: Data overflow. Invalid character value for cast specification. so while importing the table we just changed the datatype to nchar in the edit sql and the table is imported successfully. Once the table is imported, we tried to change the datatype back to datetime and it is not allowing us to do that. we ar getting an error: ‘tasks1’ table
– Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error converting expression to data type datetime.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.
Can anyone help me in fixing this. thank you
Preethi

You have bad data that is not convertible to DATETIME. I would verify with ISDATE(actshipdt) what data is convertible and what not. —
Frank Kalis
Microsoft SQL Server MVP
Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
Webmaster:http://www.insidesql.de

I did that and the out put i got is 0 that means it is not a valid date right??

May be your column has invalid character and some of the values may be > 24 characters… Use "isdate" function to check the data in the column… select * from tablename where isdate(colname) = 0 MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

It returned some rows. so this means that all the rows it returned can not be converted. Am I right??

Yes…
If it returns zero it is invalid date… and if returns 1 is valid… MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

So what can I do to convert it back to date time.

If the value is not date value then you are out of luck…
But you can update the data which date vlues and for non date values you can do manual update based on how many rows you got… 1. Create new table… with dattime column
2. Copy the valid datetime value data using isdate fuction…
3. Restore of them you can manually modify and copy the data or you can use null value…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

I think isdate is not reliable like isnumeric
select isdate(2004), isdate(‘2004’),isdate(‘31102006’)
Madhivanan Failing to plan is Planning to fail

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Madhivanan I think isdate is not reliable like isnumeric select isdate(2004), isdate(‘2004’),isdate(‘31102006′) Madhivanan Failing to plan is Planning to fail <hr height="1" noshade id="quote"></blockquote id="quote"> It depends… [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] In your first example you’ve supplied the INTEGER value of 2004, which SQL Server will evaluate to the 2004th day since its base date. The second example is the string representation of ‘2004’. SQL Server will evaluate that to the year 2004. Since you haven’t provided any month or day information, it will assume the first day of that given year. The third example is the string representation of ‘31122006’. I, here in Germany, would probably know how to interpret that string as DATETIME. I would imagine (and add) tokens like ‘31.12.2006’. However, SQL Server will not make such guesses. If it would make guesses, it would have to evaluate many, many possible variations and I guess it is more reliable to say NO in this regard and don’t accept the string as convertible to DATETIME. – Frank Kalis Microsoft SQL Server MVP Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a> Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

Well. Assume that the source dates are expressed in ddmmyyyy format and stored in varchar datatype When importing data, it is not correct way to use isdate function since dates are expressed in different format that isdate cant understand. In this case I prefer importing data in stage table, and make those strings to proper dates and then copy to target table [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

ISDATE() is a quick way to identify misbehaving data. Clearly not the most safest or "best" way to deal with the this sort of problems. Staging tables seem to be much more appropriate. And yes, of course, one has to know the implication it has when you pass an INTEGER value to a DATETIME column and when pass a STRING value to such a column. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a> Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

just to add to the other comments : If you use a visual basic dateandtime object to feed sqlserver, it will be in the correct format; so conversion may not be needed. If you know your dateformat, use convert(datetime, actshipdt, yourformat) check bol !

Thanks Everyone for the help.
We fixed the issue. we first imported the table to Access and took it to sql from there.

]]>