Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2011

    Unanswered: Deleting junk values from date column

    Hi All,

    I am facing this peculiar issue to remove junk values from the date column which has defined as VARCHAR(2).

    My issue is the Date field has been defined as VARCHAR(2) and so the users have been populating this field with some rubbish values along with some date values.

    Can you please someone advise how to remove this values and put it as an exception into other table.

    FYI: the values are something like this.

    Row1: 01/01/2008
    Row2: JAN/FEB 3
    row 3: 01/02/2008
    Row 4: jan/feb 2


    I just want to keep row1 and row3 along with other columns and split the other row2 and row4 in the exception table.

    can somebody please guide me.



  2. #2
    Join Date
    Jan 2004
    Croatia, Europe
    Provided Answers: 5
    Using PL/SQL, in a loop through all records, apply TO_DATE function to that column (using format mask you find appropriate). Put exceptions into another table. Something like this:
      l_date date;
    for cur_r in (select id, date_value from your_table) loop
        l_date := to_date(cur_r.date_value, 'dd/mm/yyyy');
        when others then
          insert into log_table ...
    end loop;
    Note that use of WHEN OTHERS is usually not recommended, but in this case (as you don't really care which error it is - value simply isn't in a format you expected it to) it is acceptable. If you wish, you can develop this suggestion and include various format masks, exception handlers etc.

    Anyway, if possible, modify that column to DATE data type.

Posting Permissions

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