SQL Server Performance

Datatype Conversion

Discussion in 'SQL Server 2005 General DBA Questions' started by preethi.talapanuri, May 29, 2007.

  1. preethi.talapanuri New Member

    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






  2. FrankKalis Moderator

  3. preethi.talapanuri New Member

    I did that and the out put i got is 0

    that means it is not a valid date right??
  4. MohammedU New Member

    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.
  5. preethi.talapanuri New Member

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

    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.
  7. preethi.talapanuri New Member

    So what can I do to convert it back to date time.
  8. MohammedU New Member

    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.
  9. Madhivanan Moderator

    I think isdate is not reliable like isnumeric
    select isdate(2004), isdate('2004'),isdate('31102006')


    Madhivanan

    Failing to plan is Planning to fail
  10. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Madhivanan</i><br /><br />I think isdate is not reliable like isnumeric<br />select isdate(2004), isdate('2004'),isdate('31102006')<br /><br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />It depends... [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />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.<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a><br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  11. Madhivanan Moderator

    Well. Assume that the source dates are expressed in ddmmyyyy format and stored in varchar datatype<br />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=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  12. FrankKalis Moderator

    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. <br /><br />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=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a><br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  13. alzdba Member

    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 !
  14. preethi.talapanuri New Member

    Thanks Everyone for the help.


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

Share This Page