2 Replies Latest reply: Aug 2, 2013 12:48 AM by Ashu_Neo RSS

    Concat Strings in to DATE

    user642208

      Hi Experts,

       

       

      I have a requirement to concat two columns and load in to single DATE column. But in one the column data it has some junk charcater, in that case i want to OMIT those and load only clean records.

       

      ARR_TIMEARR_DATE
      09:00 AM 01/01/2013
      10:30  PM24/10/2013
      9:00 AM01/10/2010
      :01/01/2013
      8:00  PM02/03/2013

       

       

      DESIRED OUTPUT:

       

      ARR_DATE_TIME

      01/01/2013 09:00 AM

      24/10/2013 10:30 PM

      01/10/2010 09:00 AM

      02/03/2013 8:00  PM

       

      Please note, there are : and extra spaces before AM/PM. I want to clean it and load. Can you please let me know what SQL will make it?

       

      Any help will be highly appreciated !!

       

      Thanks,

      K

        • 1. Re: Concat Strings in to DATE
          Manik

          Check this for template..

          We need more examples.

           

          -----------------------------------------------

           

          ALTER SESSION SET nls_date_format ='dd/mm/yyyy HH:MI AM';

          WITH t AS
                  (SELECT ':' str1, '01/01/2013' str2 FROM DUAL
                   UNION ALL
                   SELECT '9:00 AM', '01/10/2010' FROM DUAL
                   UNION ALL
                   SELECT '8:00  PM', '02/03/2013' FROM DUAL
                   UNION ALL
                   SELECT '10:30  PM', '24/10/2013' FROM DUAL
                   UNION ALL
                   SELECT '09:00 AM', '01/01/2013' FROM DUAL
                   UNION ALL
                   SELECT '9899:43434438', NULL FROM DUAL)
          SELECT TO_DATE (str2 || ' ' || REPLACE (str1, ' '), 'DD/MM/YYYY HH:MIAM') DT
            FROM t
          WHERE     NVL (SUBSTR (str1, 1, INSTR (str1, ':') - 1), -1) >= 0
                 AND SUBSTR (str1, INSTR (str1, ':') + 1, 2) >= 0
                 AND SUBSTR (str1, -2) IN ('AM', 'PM')


          -----------------

          Output:

          -----------------

           

          Session altered.

           

           

          DT

           

          -------------------------------------------------------

           

          01/10/2010 09:00 AM

           

          02/03/2013 08:00 PM

           

          24/10/2013 10:30 PM

           

          01/01/2013 09:00 AM

           

           

          4 rows selected.

           

           

          Cheers,

          Manik.

          • 2. Re: Concat Strings in to DATE
            Ashu_Neo

            Like this?

            SQL> alter session set nls_date_format = 'DD/MM/YYYY HH:MI AM'
              2  /

            Session altered.
            SQL>
            SQL> with t as
              2          (select ':' str1, '01/01/2013' str2 from dual
              3           union all
              4           select '9:00 AM', '01/10/2010' from dual
              5           union all
              6           select '8:00  PM', '02/03/2013' from dual
              7           union all
              8           select '10:30  PM', '24/10/2013' from dual
              9           union all
            10           select ':12:00 PM', '24/10/2013' from dual
            11           union all
            12           select '09:00 AM', '01/01/2013' from dual
            13           union all
            14           select '12345', null from dual)
            15  select to_date(str2 ||' '||str1, 'DD/MM/YYYY HH:MI AM') dt
            16    from t
            17  where 1=1
            18  and regexp_like(replace(str1,' '),'(^(\d){1,2}+\:(\d){1,2}+((A|P)M))')
            19  /

            DT
            -------------------
            01/10/2010 09:00 AM
            02/03/2013 08:00 PM
            24/10/2013 10:30 PM
            01/01/2013 09:00 AM

            SQL>