3 Replies Latest reply: Jan 23, 2013 5:00 AM by HeretoHelp:Prem RSS

    to_date query nt working properly

    948041
      SELECT auction_header_id,
      ABSTRACT_DETAILS,
      AUCTION_STATUS,CLOSE_BIDDING_DATE,INT_ATTRIBUTE1,
      to_date(Int_attribute1,'yyyy-mm-dd hh24:mi:ss')
      FROM PON_AUCTION_HEADERS_ALL
      WHERE auction_header_id=xyz;
      The above qury resulting error
      ORA-01830: date format picture ends before converting entire input string.
        • 1. Re: to_date query nt working properly
          HeretoHelp:Prem
          It looks like data in TIMEGROUP.TIME_GROUP_STARTED/TIME_GROUP_ENDED column(s) do not match 'HH24:MI' format - some of them are longer.


          Please tryu to alter the session and run the query again.

          Steps Could Be:

          =================
          Current Query Having Error:
          =================
          SQL> select to_date( '03-APR-2012 11:550', 'DD-MON-YYYY HH24:MI' ) from dual;
          select to_date( '03-APR-2012 11:550', 'DD-MON-YYYY HH24:MI' ) from dual
          *
          ERROR at line 1:
          ORA-01830: date format picture ends before converting entire input string

          ==================
          Alter The Session
          ==================
          SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

          Session altered.


          ==================
          Now Run the same Query:
          ==================
          select to_date( '03-APR-2012 11', 'DD-MON-YYYY HH24:MI' ) from dual;

          TO_DATE('03-APR-201
          -------------------
          2012-04-03 11:00:00

          1 row selected.

          -----


          Hope this Help ...!!!!!!!!!!!!!!!!!


          Regards,
          Prem
          • 2. Re: to_date query nt working properly
            948041
            No it's not working.
            Actually the Int_attribute1 is of varchar2 type in table......to this iaded tenderbox opendate in oaf personalizations it is storing in the correct format but in OAF page it showing as 12-jan-5048 like this
            • 3. Re: to_date query nt working properly
              HeretoHelp:Prem
              Ok fine.

              Could you please let me know , in which format you are storing data in int_attribute1 (Varchar2) .

              Or execute the below query n paste output here..

              SELECT Int_attribute1
              FROM PON_AUCTION_HEADERS_ALL
              WHERE auction_header_id=xyz;


              Regards,
              Prem