Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2004
    Posts
    31

    Red face Unanswered: converting date field from Informix to SQL nvarchar

    I copying data from our Informix 7.2 database into SQL Server 2K using DTS but hitting errors during the process. There appears to be date data within Informix that will not convert properly when moving into SQL. Since the error is appearing at the 1.5million (approx.) record. I figured on changing from datetime to nvarchar. Works like a charm! :-)

    My new problem is converting it back to datetime so I can query against the date without having to create scripts to parse the field.

    The data in SQL currently looks like this -> 2000-11-29 (nvarchar(50))
    I would like to have it -> 11/29/00 (datetime)

    Any help is greatly appreciated!

    JT


    The goodness we share is rewarded twice!

  2. #2
    Join Date
    Dec 2003
    Posts
    31
    try to find the record that cause the failure:
    select * From <your table name> where isdate(<date varchar column name>)=0

    varchars in yyyy-mm-dd format usually converted to dates data type without problems...

  3. #3
    Join Date
    Apr 2004
    Posts
    31
    Thank you for the reply.

    So your saying, if the table name is findet (financial detail) and the column name is fdate (nvarchar, 50).

    Run the select against the varchar column?

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You've got data that can't be converted to date...

    To see the offending rows....

    SELECT * FROM findet WHERE ISDATE(fdate) = 0
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Apr 2004
    Posts
    31
    OK. I am testing it now. When I attempted to convert the column in Enterprise Mgr, I got the following error:

    /*

    Tuesday, April 13, 2004 13:20:17

    User: sa

    Server: NYCRPSTOR01

    Database: Mysis

    Application: MS SQLEM - Data Tools

    */



    'findet' 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.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by Brett Kaiser
    You've got data that can't be converted to date...

    To see the offending rows....

    SELECT * FROM findet WHERE ISDATE(fdate) = 0
    Did you run this?

    What did you get?

    $1,000 bucks you've got non date data in that column
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Apr 2004
    Posts
    31
    That worked!!! Thank you for your help!!!

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What worked?

    Did you find non date data?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Dec 2003
    Posts
    31
    before you change the column type find what cause the conversion error!

  10. #10
    Join Date
    Apr 2004
    Posts
    31
    There was data mis-entered 0004-04-10 (example). Since the year is out of range, converting it to datetime was not possible.

    Now, how do I script my DTS package to exclude records that are out of range and put them in a separate table for exception reporting?

    Again, thank you for your help! It is greatly appreciated!!!

  11. #11
    Join Date
    Dec 2003
    Posts
    31
    just load the entire data to table with varchar column then copy the wrong date format to errors table and delete these records from the original table, convert the good data from the original table into target table drop the original table fix the failed record and enter them into the target table

Posting Permissions

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