13 Replies Latest reply on Jan 20, 2012 2:05 PM by Nicosa-Oracle

    error in converting varchar into dd/mm/yyyy

    890271
      Hi all,

      i need to insert the "date_of_birth" in 'dd/mm/yyyy'.
      but in master table "date_of_birth" column is in Varchar2(15),and values in the master tables are not in proper format.
      i.e) some values are like *'21-jan-1988'*,
      and some are like *'01/21/1988'* and *'21-01-1988'*.

      i need to insert into a new table in dd/mm/yyyy format.

      thanks in advance.
        • 1. Re: error in converting varchar into dd/mm/yyyy
          €$ħ₪
          SQL> alter session set nls_date_format='DD/MM/YYYY';
          
          Session altered.
          
          SQL> with t as
          (
          select '01/02/1999' as c1 from dual union all
          select '01-JAN-2011' as c1 from dual union all
          select '13-11-2011' as c1 from dual
          )
          select TO_DATE(c1,'DD/MM/YYYY') from t ;
          
          TO_DATE(C1
          ----------
          01/02/1999
          01/01/2011
          13/11/2011
          1 person found this helpful
          • 2. Re: error in converting varchar into dd/mm/yyyy
            Etbin
            and some are like '01/21/1988' and '21-01-1988'.
            How would you treat *'01/02/1999'* and *'02-01-1999'* ? Are the two dates the same or are they different ?

            Regards

            Etbin
            • 3. Re: error in converting varchar into dd/mm/yyyy
              890271
              BOTH ARE SAME.
              • 4. Re: error in converting varchar into dd/mm/yyyy
                890271
                Some Dates Are like
                13-12-2011---dd-mm-yyyy
                12-13-2011---mm-dd-yyyy

                FOR THIS CASE HOW CAN I USE.
                • 5. Re: error in converting varchar into dd/mm/yyyy
                  Etbin
                  Assuming date_of_birth will be stored as a date
                  select case when length(date_of_birth) = 11
                              then to_date(date_of_birth,'dd-mon-yyyy')
                              when instr(date_of_birth,'/') > 0
                              then to_date(date_of_birth,'mm/dd/yyyy')
                              when instr(date_of_birth,'-') > 0
                              then to_date(date_of_birth,'dd-mm-yyyy')
                         end date_of_birth
                    from your_table
                  Regards

                  Etbin

                  Edited by: Etbin on 19.1.2012 9:19
                  Considering the post above you didn't give a correct answer to my question => my answer is not useful since you have both dd-mm-yyy and mm-dd-yyyy dates and Murphy's law can provide both mm/dd/yyyy and dd/mm/yyyy too
                  I see no way how to get things right
                  1 person found this helpful
                  • 6. Re: error in converting varchar into dd/mm/yyyy
                    BluShadow
                    Etbin said:
                    >
                    How would you treat '01/02/1999' and '02-01-1999' ? Are the two dates the same or are they different ?
                    >

                    and your replied:
                    BOTH ARE SAME
                    Really? Are you sure?

                    So you are saying that you know that if a date is specified with "/" seperators then the format of the day and month must be the opposite to a date specified with "-" seperators?

                    The point Etbin is making is that if you have those two dates, are they referring to 2nd January 1999 or 1st February 1999 and if they've been entered as text on the database rather than dates, how do you know what the initial intention was behind the format used? To me, if the application that created these dates all came from one source in one country then I would have to assume that the dates are different, and let's say the data is in the UK, then I would have to assume that people have entered the dates in DD MM YYYY format, so the first date is 1st February 1999 and the second one is 2nd January 1999.

                    You're going to have to define the logic first before you can determine how to turn your strings into dates.

                    Then go and seriously tell off the person who designed such a sh!t database in the first place who didn't think that dates should be stored as dates.
                    1 person found this helpful
                    • 7. Re: error in converting varchar into dd/mm/yyyy
                      TPD-Opitz
                      887268 wrote:
                      Some Dates Are like
                      13-12-2011---dd-mm-yyyy
                      12-13-2011---mm-dd-yyyy

                      FOR THIS CASE HOW CAN I USE.
                      This problem has no generic solution. There is no other way than having a human looking at the records.

                      For this example one meight say that +13+ cannot be a month. But there is no such rule for other dates like
                      2-3-4 (2nd mar 2004 or 3rd feb 2004 or 4th mar 2002...)
                      11-10-12 (...)

                      This is the reason why dates should ever be stored as <tt>DATE</tt> type!

                      I'd try to refuse this task and schedule it to the one than made the decision to store dates as varchars.

                      bye
                      TPD
                      1 person found this helpful
                      • 8. Re: error in converting varchar into dd/mm/yyyy
                        890271
                        the database having more than 10 lakh records, and most of them are
                        from old data migration. i.e) from older manually written books. so they kept the dob as varchar.

                        thanks for all, i informed the complexity in this to my client. im waiting for their response.

                        thank you all ..........................
                        • 9. Re: error in converting varchar into dd/mm/yyyy
                          398200
                          Unless you have a set pattern and consistancy of data entered in those pattern, logic cannot be biult.

                          Eg: If you have only these two patterns of data entry dd-mm-yyyy , dd/mm/yyy, yyyy/dd/mm, yyyy dd mm,dd mm yyyy, mm dd yyyy.

                          So accordingly a logic can be writted to convert them into string. But you have to be 200% sure that respective data available in the table is specific format follow this format consistantly. Else there are chances that month & dates & year(if used in two digit) would be interchanged.

                          This problem could have been averted during migration where you could have better idea of the date available in the data to be migrated.

                          If you keep these 10lakh records in different bucket with reference to their origin. Then you can get some of the bucket which have high level of consistancy to have simple logic of date conversion. And then the remaining dirty buckets need to be cleaned carefully with logic or manually.

                          what do you say?

                          rdgs.
                          1 person found this helpful
                          • 10. Re: error in converting varchar into dd/mm/yyyy
                            890271
                            Ya sure, We are planning on this in a way which is similar to your opinion.

                            Thanks You for taking Valuable time and reply on this,,,,,,,,

                            thanks again.
                            • 11. Re: error in converting varchar into dd/mm/yyyy
                              Etbin
                              Is there any chance of having access to other data sources (our Identity Cards contain date of birth, we used to have Person Identification Numbers that included date of birth, but were banned because date of birth counts as personal data and was fully displayed ...).
                              You might not be successful as the legislation usually treats date of birth as personal data subject to various restrictions when being handled.
                              Clever lawyers might be able to find workarounds.

                              Regards

                              Etbin
                              • 12. Re: error in converting varchar into dd/mm/yyyy
                                890271
                                hi etbin..

                                im not having such privilage.

                                im using a "database link" (DBLINK) to collect the data's from clint db and made some process and insert in to client's db using the dblink.
                                In this Process im not able to see the data's.........in getting "not a valid month error". so they provide the format only. i.e) the date is in not proper format.
                                • 13. Re: error in converting varchar into dd/mm/yyyy
                                  Nicosa-Oracle
                                  You could use such a method, but WITH NOT GUARANTEE AT ALL_ that you're not inserting WRONG_ dates of birth.
                                  SQL> with rotten_data as (
                                    2  -- start of sample data --
                                    3  select '10-Jan-2010' vdate from dual
                                    4  union all select '10/Feb/2010' from dual
                                    5  union all select '10-03-2010' from dual
                                    6  union all select '10/04/2010' from dual
                                    7  union all select '17/04/2010' from dual
                                    8  union all select '11/14/2010' from dual
                                    9  union all select '23-04-2010' from dual
                                   10  union all select '07-18-2010' from dual
                                   11  union all select '01-02-2010' from dual
                                   12  -- end of sample data --
                                   13  )
                                   14  select
                                   15     vdate,
                                   16     case
                                   17             when regexp_like(r2,'^\D*$') then to_date(r1||r2||yr,'ddMonyyyy')
                                   18             when regexp_like(r1,'^\D*$') then to_date(r2||r1||yr,'ddMonyyyy')
                                   19             when (to_number(r1)>12 and to_number(r2)<=12) then to_date(r1||r2||yr,'ddmmyyyy')
                                   20             when (to_number(r1)<=12 and to_number(r2)>12) then to_date(r2||r1||yr,'ddmmyyyy')
                                   21             else to_date(r1||r2||yr,'ddmmyyyy')
                                   22     end newdt
                                   23  from (
                                   24     select
                                   25             vdate
                                   26             ,regexp_substr(vdate,'[^-/]+',1,1) r1
                                   27             ,regexp_substr(vdate,'[^-/]+',1,2) r2
                                   28             ,regexp_substr(vdate,'[^-/]{4}',1,1)yr
                                   29     from rotten_data
                                   30  ) ;
                                  
                                  VDATE       NEWDT
                                  ----------- -------------------
                                  10-Jan-2010 10/01/2010 00:00:00
                                  10/Feb/2010 10/02/2010 00:00:00
                                  10-03-2010  10/03/2010 00:00:00
                                  10/04/2010  10/04/2010 00:00:00
                                  17/04/2010  17/04/2010 00:00:00
                                  11/14/2010  14/11/2010 00:00:00
                                  23-04-2010  23/04/2010 00:00:00
                                  07-18-2010  18/07/2010 00:00:00
                                  01-02-2010  01/02/2010 00:00:00
                                  
                                  9 rows selected.
                                  This would still fail if the data is not one of those format +(or is not a valid date whatever the format)+ :
                                  - dd/mm/yyyy
                                  - mm/dd/yyyy
                                  - dd-mm-yyyy
                                  - mm-dd-yyyy
                                  - dd-Mon-yyyy
                                  - dd/Mon/yyyy

                                  if one of the value is > 12 then it consider it as the day, the other one being the month.
                                  If both are <=12, then it assumes the first is the day (dd).

                                  Without any additionnal information, you cannot really make it better...