Results 1 to 8 of 8

Thread: dates

  1. #1
    Join Date
    Dec 2010
    Posts
    2

    Unanswered: dates

    i am trying to move data from one column (varchar) into a new column in another table (timestamp). the data in the varchar column are dates (when not null) but they are in a wide variety of formats...
    1/1/2011
    1/1/11
    01/01/2011
    2011-01-01

    when trying to write this data over I keep getting errors like "not a valid month". How can you clean up all of these types of dates so you have one format so you can use something like a to_date?

  2. #2
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Oh, you're screwed. LOL.

    I wouldn't try doing this in Oracle, as it's regex implementation is pretty rudimentary. I'd use a language with good regex support like Python, Perl or Ruby to parse the dates. I would also recommend changing that column from varchar to date if you can.

  3. #3
    Join Date
    Dec 2010
    Posts
    2

    way to seperate

    do you know a way to search through to find all of the types of date formats?

    and i agree with your first statment btw

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >do you know a way to search through to find all of the types of date formats?
    Iteratively until every non-NULL entry populates the new DATE datatype column
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by artacus72 View Post
    I wouldn't try doing this in Oracle, as it's regex implementation is pretty rudimentary.
    Hmm, as far as I know the full range of regex is supported.

    But that is not the real problem.
    The problem is knowing what a specific format actually means. The ISO style dates '2010-01-03' are simple and should be relatively "stable", but what about the others

    Is 01/02/2011 the first of february (european style date) or the second of january (american style date). What about 10/11/12? Is that October 11th, 2012? Or is that November 12th, 2010?

    Unless there is some rule that says that any date with a / is always m/d/y (or whatever) then this could work even with Oracle's regex implementation.

    I would also recommend changing that column from varchar to date if you can.
    That's exactly what the OP is trying to do.


    I would use Oracle's error logging features together with a multiple-update approach:

    First create the error log table:
    Code:
    EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('THE_TABLE', 'DATE_ERRORS');
    THE_TABLE is the name of the table to be changed, DATE_ERRORS is the table that is created by Oracle to hold the error information.

    Code:
    -- Migrate the ISO style dates
    UPDATE the_table
      SET new_column = to_date(old_column, 'YYYY-MM-DD')
    WHERE regexp_like(old_column, '[0-9]{4}-[0-9]{2}-[0-9]{2}')
    LOG ERRORS INTO DATE_ERRORS ('Date migration ISO style')
    REJECT LIMIT UNLIMITED;
    
    -- Migrate american style dates
    UPDATE the_table
      SET new_column = to_date(old_column, 'MM/DD/YYYY')
    WHERE regexp_like(old_column, '[0-9]{1,2}/[0-9]{1,2}/[0-9]{2,4}'); 
    LOG ERRORS INTO DATE_ERRORS ('Date migration others')
    REJECT LIMIT UNLIMITED;
    This will update all rows where possible.

    Rows where the date conversion fails will not be changed and logged into DATE_ERRORS.
    You can use the following statement to view them and take approriate actions:
    Code:
    SELECT t.old_column, err.ora_err_mesg$
    FROM date_errors err 
      JOIN the_table t ON t.rowid = err.ora_err_rowid$;
    You might want to play around with the regular expressions

  6. #6
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    You can try something like the below code:
    Code:
    with 
      date1 as ( select '1/1/2011'   as char_date from  dual ),
      date2 as ( select '1/1/11'     as char_date from  dual ),
      date3 as ( select '01/01/2011' as char_date from  dual ),
      date4 as ( select '2011-01-01' as char_date from  dual ),
      dates as ( select trim( char_date ) as char_date from date1 union
                 select trim( char_date ) as char_date from date2 union
                 select trim( char_date ) as char_date from date3 union
                 select trim( char_date ) as char_date from date4 )
    select char_date,
           ( case
               when instr( char_date, '-' )    > 0   then to_date( char_date, 'yyyy-mm-dd' )
               when substr( char_date, -3, 1 ) = '/' then to_date( char_date, 'mm/dd/yy'   )
               when instr( char_date, '/' )    > 0   then to_date( char_date, 'mm/dd/yyyy' )
             end ) as date_col
     from  dates


    Code:
    CHAR_DATE         DATE_COL
    01/01/2011   1/1/2011 12:00:00 AM
    1/1/11       1/1/2011 12:00:00 AM
    1/1/2011     1/1/2011 12:00:00 AM
    2011-01-01   1/1/2011 12:00:00 AM
    hth

  7. #7
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Quote Originally Posted by shammat View Post
    Hmm, as far as I know the full range of regex is supported.
    The problem with Oracle regex is that it has no support for grouping so it will take much longer for you to write complicated regexp_like and regexp_substr statements than it would be to pull it out with groups.

  8. #8
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by artacus72 View Post
    The problem with Oracle regex is that it has no support for grouping
    Ah, thanks.
    I didn't realize that (never needed to use grouping so far...)

Posting Permissions

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