Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2003
    Posts
    6

    Unhappy Unanswered: how to retreive the corrupt data with a "date" type

    Hello, all experts.

    I got a very troublesome problem. I have a table as below,
    table a
    col1 serial,
    col2 date
    DBDATE=DMY4
    select * from a;
    1210: Date could not be converted to month/day/year format
    Seems there are some records has invalid date in "col2".
    col1 col2
    1 01/10/2003
    2 01/10/2003
    3 ??
    4 01/10/2003
    5 03/10/2003
    6 01/11/2003
    7 ??
    8 ??
    9 ??
    ...
    ...
    1000 ??
    1001 ??
    ...

    I want to update these error records with a valid date at the same time remaining the correct records,
    update a set col2='10/10/2002' where col2<'01/01/1900'
    but I still got this error,
    1210: Date could not be converted to month/day/year format
    how can I do?
    In addition, why are these corrupt data populated? I never inserted any invalid date into the table, actually I think even I want to insert an invalid date, it will be rejected by informix, right?

    I will be very appreciated if anyone can give me a hand. Thanks in advance

  2. #2
    Join Date
    Aug 2002
    Location
    Bonn/Germany
    Posts
    152
    Run 'oncheck -cDI <dbname>:<tablename>' on
    your table.

    It seems that some data is corrupted in your system.
    Check also the 'online.log' for any additional
    error messages.

    Best regards

    Eric
    --
    IT-Consulting Herber
    WWW: http://www.herber-consulting.de
    Email: eric@herber-consulting.de

    ***********************************************
    Download the IFMX Database-Monitor for free at:
    http://www.herber-consulting.de/BusyBee
    ***********************************************

  3. #3
    Join Date
    Jun 2003
    Posts
    6
    Originally posted by eherber
    Run 'oncheck -cDI <dbname>:<tablename>' on
    your table.

    It seems that some data is corrupted in your system.
    Check also the 'online.log' for any additional
    error messages.
    It is informix SE not IDS, I use bcheck, the output seems ok. I really don't how these corrupt data generated and how to retreive and fix.

  4. #4
    Join Date
    Aug 2002
    Location
    Bonn/Germany
    Posts
    152
    My memories from I-SE are far away.
    Does unsetting the DBDATE variable fix the problem ?

    What might be possible (at least with IDS) is that you
    convert your date-datatype to an integer (because
    a date is internally stored as an integer in IFMX):

    alter table <tablename> modify (<colname> int);

    Then you need to fix the 'corrupt' date values and re-convert
    your datatype:

    alter table <tablename> modify (<colname> date);


    The better way however might be to call IFMX tech support.

    Best regards

    Eric
    --
    IT-Consulting Herber
    WWW: http://www.herber-consulting.de
    Email: eric@herber-consulting.de

    ***********************************************
    Download the IFMX Database-Monitor for free at:
    http://www.herber-consulting.de/BusyBee
    ***********************************************

  5. #5
    Join Date
    Jun 2003
    Posts
    6
    Originally posted by eherber
    My memories from I-SE are far away.
    Does unsetting the DBDATE variable fix the problem ?

    What might be possible (at least with IDS) is that you
    convert your date-datatype to an integer (because
    a date is internally stored as an integer in IFMX):

    alter table <tablename> modify (<colname> int);

    Then you need to fix the 'corrupt' date values and re-convert
    your datatype:

    alter table <tablename> modify (<colname> date);


    The better way however might be to call IFMX tech support.
    It doesn't work to unset DBDATE, but it is really a good way to alter table.
    Eherber. Thank u very much for ur kind help.

  6. #6
    Join Date
    Oct 2002
    Location
    Pakistan (Lahore)
    Posts
    16
    Originally posted by eherber
    Run 'oncheck -cDI <dbname>:<tablename>' on
    your table.

    It seems that some data is corrupted in your system.
    Check also the 'online.log' for any additional
    error messages.

    Plz. check your env the value of DBDATE environment variable shpuld be mdy4/

    I hope it will resolve ur problem.


    wicky

Posting Permissions

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