7 Replies Latest reply: May 10, 2012 7:50 PM by Paulie RSS

    date formats

    Lucky
      Hi , i have a varchar column in a table in that date values have been stored.
      my problem is like in that column the date values are stored in different formats.

      i need to identfy the formats and to update them in mm-dd-yyyy format back to table.


      is there any way to do it

      ex:
      the column is samp varchar2(99);

      i that i have date values in

      several formats like

      26.05.2011 18:43:38
      31/05/2011 12:05:09
      17/06/2011 6:06:08 PM
      22.06.2011 15:26:08
      6/14/2011 16:19
      16/06/2011 5:43:35 PM
      17-Jan-12

      need to identify the formats and to update them properly in mm-dd-yyyy hh:mi:ss format

      pls suggets the way to do it
        • 1. Re: date formats
          HuaMin Chen
          Make sure every line is in this format " mm-dd-yyyy hh:mi:ss format"

          then transfer it to a a date like
          select to_date('05-13-2012 13:03:40','mm-dd-yyyy hh:mi:ss') from dual;
          • 2. Re: date formats
            713555
            you've probably given this thought already but if you dont know the format it was logged as how do you know whether 01/02/2012 is first of jan or second of february.

            how do you know whether 6:00 is 6am or 6pm if the am or pm isnt given?


            You have to make some assumptions during transformation. youve a bit of coding to do.
            Either create a plsql package to loop through the data and catch all exceptions and transform dots and dashes to / and then look for pm and am and transform.

            or to do do it all in sql
            create a new table with several transformations columns. lets say 5.

            create table trans_col (pk_id, date1 varchar2, date2 varchar2, date3 varchar2, date4 varchar2, date5 date)

            insert into trans_col (pk_id, date1) values (select pk_id, bad_Date_col from master_table)

            then go through the iterations of transforming the dates, change dots and dashes to /
            update trans_Col set date2= replace (date1, '.','/')

            replace months with numbers.
            update trans_Col set date3= replace (date1, 'JAN','01') ... etc

            guess the time from pm or am and transform. keep updating the next columns and viewing the results. you should really do it with a plsql procedure though.
            • 3. Re: date formats
              EdStevens
              Lucky wrote:
              Hi , i have a varchar column in a table in that date values have been stored.
              my problem is like in that column the date values are stored in different formats.

              i need to identfy the formats and to update them in mm-dd-yyyy format back to table.
              You don't want to "update them in mm-dd-yyyy format". You want to transform them from the garbage they already are, into a proper DATE column.

              Suggestions already made can help with the identification/transform process, but if, in the end, you don't put them into a DATE column, you are just setting yourself up for repeating the same exercise on a regular basis.
              >
              is there any way to do it

              ex:
              the column is samp varchar2(99);

              i that i have date values in

              several formats like

              26.05.2011 18:43:38
              31/05/2011 12:05:09
              17/06/2011 6:06:08 PM
              22.06.2011 15:26:08
              6/14/2011 16:19
              16/06/2011 5:43:35 PM
              17-Jan-12

              need to identify the formats and to update them properly in mm-dd-yyyy hh:mi:ss format

              pls suggets the way to do it
              • 4. Re: date formats
                Mark Malakanov (user11181920)
                You want to transform them from the garbage they already are, into a proper DATE column.
                yes, and make some clumsy application go down.
                Wrong apps must die! :)
                • 5. Re: date formats
                  Mark Malakanov (user11181920)
                  I would write a function that normalizes data from a set of known "bad" formats into a proper one.

                  create or replace
                  function normalize_string_date(sd in varchar2) return varchar2 as
                  type t_formats is table of string(60) not null;
                  formats t_formats := t_formats('DD.MM.YYYY HH24:MI:SS','DD-MM-YYYY HH24:MI:SS');
                  S string(60);
                  begin
                  for f in 1..formats.count loop
                  begin
                  S := to_char(to_date(sd,formats(f)),'mm-dd-yyyy hh24:mi:ss');
                  return S;
                  exception when others then null;
                  end;
                  end loop;
                  return SD;
                  end;


                  then call it in update statement
                  • 6. Re: date formats
                    JustinCave
                    As has already been pointed out, when you do this clean-up, you really want to store the data in a DATE column. If your application really, really needs a string, you can create a view (or, in 11g, a virtual column) that exposes the data as a VARCHAR2 in the appropriate format.

                    You will have to define a hierarchy of formats to try. So if you have a string '01/02/03 04:05:06', you'll need to determine whether you want to treat that as January 2, 2003, February 1, 2003, or February 3, 2001 and whether the time should be 4:05:06 AM or PM. Once you do, you can write a function along the lines of
                    CREATE OR REPLACE FUNCTION my_to_date( p_str IN VARCHAR2, p_format IN VARCHAR2 )
                      RETURN DATE
                    IS
                      l_date DATE;
                    BEGIN
                      l_date := to_date( p_str, p_format );
                      RETURN l_date;
                    EXCEPTION
                      WHEN others THEN
                        RETURN null;
                    END;
                    
                    CREATE OR REPLACE FUNCTION gunk_to_date( p_str IN VARCHAR2 )
                      RETURN DATE
                    IS
                      l_date DATE;
                      TYPE typ_format_mask_arr IS TABLE OF VARCHAR2(30);
                      l_format_masks typ_format_mask_arr := new typ_format_mask_arr( 'mm-dd-yyyy', 'mm.dd.yyyy hh24:mi:ss', <<more masks>> );
                    BEGIN
                      FOR i IN 1..l_format_masks.COUNT
                      LOOP
                        l_date := my_to_date( p_str, l_format_masks(i) );
                        IF( l_date IS NOT NULL )
                        THEN
                          RETURN l_date;
                        END IF;
                      END LOOP;
                      RETURN null;
                    END;
                    You'll need to list the format masks you want to try in the order you want to try them when you initialize l_format_masks in gunk_to_date.

                    Justin
                    • 7. Re: date formats
                      Paulie
                      >

                      Not with a database like this, you're not (lucky, that is)!

                      i need to identfy the formats and to update them in mm-dd-yyyy format back to table.
                      is there any way to do it
                      Yes, and here it is
                      Pseudocode
                      
                      Function VarOut Good_Date Crystal_Ball(VarIn Bad_Date)
                      {
                        Return  Take_Completely_Wild_Guess(Bad_Date);
                      }
                      Please excuse sarcasm, but you are in a bit of a quandry.

                      the column is samp varchar2(99);
                      I've seen some doozies in my time, but a
                      Date    VARCHAR2(99)
                      that's up there with the best of them. ;)

                      It opens up all sorts of fascinating possibilities - do you have any dates in this form?
                      "In this the year of our Lord 1798, the 3rd day of Thermidor in the glorious reign of His Imperial Majesty, Napoleon I"
                      ;)
                      26.05.2011 18:43:38
                      31/05/2011 12:05:09
                      17/06/2011 6:06:08 PM
                      22.06.2011 15:26:08
                      6/14/2011 16:19
                      16/06/2011 5:43:35 PM
                      17-Jan-12
                      My first thought is that you can simplify the problem in one way.

                      You have one record "17-Jan-12" and then others are precise to the second. This is logically ridiculous.

                      Get rid of the time field and set the time (when you've figured out the date of course) to midday. There's no
                      point in trying to make data more precise than it could possibly be - and eliminating the time will simplify
                      things.

                      How many records are in this database? Do have a paper-trail? If not, you're screwed, because then you
                      don't know if the record is in European or American format, since you obviously have both formats...

                      What I'm saying is that sometime computers are not enough - you may have to resort to carbon-based
                      lifeforms.

                      Best of luck.


                      Paul...