8 Replies Latest reply: Jan 12, 2012 3:41 AM by HamidHelal RSS

    Convert MON-DD-YYYY

    user528488
      Hi everyone,

      I had a process and data in .TXT file format. Im going to insert this data into a TABLE in ORACLE, then i had encounter problem regarding this format MON-DD-YYYY of date.
      How can i convert this date format from MON-DD-YYYY into DD-MON-YYYY?

      Please help me solve this problem.

      Thanks.

      Lala
        • 1. Re: Convert MON-DD-YYYY
          850050
          hi,
          How can i convert this date format from MON-DD-YYYY into DD-MON-YYYY?
          if your data base date column is date then
          to_date('JAN-15-2010','DD-MON-YYYY');
          • 2. Re: Convert MON-DD-YYYY
            user528488
            I run this quey and...

            select TO_DATE('FEB-10-2012','DD-MON-YYYY') mydate from dual;

            error: ORA-01858: a non-numeric character was found where a numeric was expected
            • 3. Re: Convert MON-DD-YYYY
              AlexAnd
              GD wrote:
              hi,
              How can i convert this date format from MON-DD-YYYY into DD-MON-YYYY?
              if your data base date column is date then
              to_date('JAN-15-2010','DD-MON-YYYY');
              wrong

              if 'JAN-15-2010' in .txt then mask 'MON-DD-YYYY'
              SQL> select to_date('JAN-15-2010','DD-MON-YYYY') from dual
                2  /
              select to_date('JAN-15-2010','DD-MON-YYYY') from dual
                             *
              ERROR at line 1:
              ORA-01858: a non-numeric character was found where a numeric was expected
              
              
              SQL> ed
              Wrote file afiedt.buf
              
                1* select to_date('JAN-15-2010','MON-DD-YYYY') from dual
              SQL> /
              
              TO_DATE(
              --------
              15.01.10
              
              SQL> 
              • 4. Re: Convert MON-DD-YYYY
                850050
                hi,
                i think you have to change the date format.
                may be this like can help you.
                Re: How to change default date format
                but when you planning to change the date format you should consider many things.
                • 5. Re: Convert MON-DD-YYYY
                  HamidHelal
                  select TO_DATE('FEB-10-2012','DD-MON-YYYY') mydate from dual;

                  error: ORA-01858: a non-numeric character was found where a numeric was expected
                  Here is the solution, check it.
                    select TO_DATE('FEB-10-2012','MON-DD-YYYY') mydate from dual;
                  OR
                   select TO_DATE(TO_CHAR(TO_DATE('FEB-10-2012','MON-DD-YYYY'),'DD-MON-YYYY'),'DD-MON-YYYY') mydate from dual;
                  Hope this will help you.


                  If someone's response is helpful or correct, please mark it accordingly.

                  Edited by: HamidHelal on Jan 12, 2012 2:14 PM
                  • 6. Re: Convert MON-DD-YYYY
                    AlexAnd
                    HamidHelal wrote:
                    select TO_DATE('FEB-10-2012','DD-MON-YYYY') mydate from dual;

                    error: ORA-01858: a non-numeric character was found where a numeric was expected
                    Here is the solution, check it.
                      select TO_DATE('FEB-10-2012','MON-DD-YYYY') mydate from dual;
                    OR
                    select TO_DATE(TO_CHAR(TO_DATE('FEB-10-2012','MON-DD-YYYY'),'DD-MON-YYYY'),'DD-MON-YYYY') mydate from dual;
                    Hope this will help you.


                    If someone's response is helpful or correct, please mark it accordingly.

                    Edited by: HamidHelal on Jan 12, 2012 2:14 PM
                    may be i don't understand correctly
                    but why date 'FEB-10-2012' convert to char and then convert to date %)
                    select TO_DATE(TO_CHAR(TO_DATE('FEB-10-2012','MON-DD-YYYY'),'DD-MON-YYYY'),'DD-MON-YYYY') mydate from dual;
                    for db
                    TO_DATE(TO_CHAR(TO_DATE('FEB-10-2012','MON-DD-YYYY'),'DD-MON-YYYY'),'DD-MON-YYYY') = TO_DATE('FEB-10-2012','MON-DD-YYYY')
                    in db a date is date
                    and
                    for OP's task
                    if 'JAN-15-2010' in .txt then mask 'MON-DD-YYYY' and then insert to db then to_date('JAN-15-2010','MON-DD-YYYY') (column date type)

                    if OP wnat to insert to .txt in format 'DD-MON-YYYY' then to_char(to_date('JAN-15-2010','MON-DD-YYYY') ,'DD-MON-YYYY')
                    • 7. Re: Convert MON-DD-YYYY
                      910597
                      Your problem is to convert any date format, MON-DD-YYYY into DD-MON-YYYY.

                      You will get your answer with explanation at the below site. Visit and then search the "Date Function". after that you will get 10 Exercise and Answer regarding Date Function. Hope u Enjoy!

                      http://sites.google.com/site/oracleflavour

                      Edited by: user11121840 on Jan 12, 2012 1:39 AM
                      • 8. Re: Convert MON-DD-YYYY
                        HamidHelal
                          select TO_DATE('FEB-10-2012','MON-DD-YYYY') mydate from dual;
                        OR
                        select TO_DATE(TO_CHAR(TO_DATE('FEB-10-2012','MON-DD-YYYY'),'DD-MON-YYYY'),'DD-MON-YYYY') mydate from dual;
                        Result is same. If the user have any confusion about the format of first one,so i give the next one. what he likes. but i prefer first one.:)
                        TO_DATE(TO_CHAR(TO_DATE('FEB-10-2012','MON-DD-YYYY'),'DD-MON-YYYY'),'DD-MON-YYYY') = TO_DATE('FEB-10-2012','MON-DD-YYYY')
                        it's right.