1 2 Previous Next 18 Replies Latest reply on Aug 15, 2017 7:16 PM by r.E.apex

    Invalid table name & column does not match format YYY_MON_DD HH24:MI:SS

    r.E.apex

      I try to convert the Varchar(30) type to Date type on Oracle APEX 5.04. I use the following SQL Query for the DatePicker item: (I used following link to get an answer) https://stackoverflow.com/questions/34329483/how-to-convert-date-to-timestampdd-mon-yyyy-hh24miss-ff-format-in-oracle/34…

      NOTE: I changed the table name to ALL_THREE_NEW_DATA. I have the type of the column as "DATE_PICKER"; FORMAT MASK as "DD-MON-YYYY HH:MIPM"; SOURCE -> TYPE as "DATABASE COLUMN"

      Where is the mistake? Or, how can I make the time format same as it is on the database?

      I think, I can't solve the following problem without changing the type.

       

       

      I tried the following:

       

      I tried the following, but got an error:

      alter table ALL_THREE_NEW_DATA add (S_DATE date);

      update ALL_THREE_NEW_DATA set S_DATE = to_date(STATUS_DATE, <<some format>>);

        • 1. Re: Invalid table name & column doesn't match format
          LA County APEX

          Have you tried this format, to_char(nvl(statud_date,sysdate),'YYYY-MM-DD HH:MI:SS AM')

          • 2. Re: Invalid table name & column doesn't match format
            Q_STEPHENSON

            Hi

             

            Firstly, let me understand this correctly. You have a string value that is in a date format and you want to display it in a different format in Apex. I assume, based on the screen shot that the format of this value is YYYY-MM-DD HH24:MI:SS.

             

            If that is the case then all you need to do is to_date the value e.g.

            SELECT TO_DATE('2016-11-09 15:16:24','YYYY-MM-DD HH24:MI:SS') mydate

            FROM DUAL.

             

            In APEX, you ensure the item type is a date picker.  In the date picker item set the date format attribute you want e.g YYYY-MM-DD HH24:MI:SS.  That is, if you don't want the default format of you application.

             

            Hope that helps

            Q

            • 3. Re: Invalid table name & column doesn't match format
              3474044

              Hello Q_STEPHENSON,

               

              You understand it correctly. The thing is, I don't know where do I need to put:

              SELECT TO_DATE('2016-11-09 15:16:24','YYYY-MM-DD HH24:MI:SS') mydate

              FROM DUAL

              .I can change the date format attribute, but can you please let em know where should I put the to_date value?

               

              Thanks!

              R

              • 4. Re: Invalid table name & column doesn't match format
                3474044

                I didn't try, but I don't know where should I put

                "to_char(nvl(statud_date,sysdate),'YYYY-MM-DD HH:MI:SS AM')"

                 

                Can you please help me to understand where do I need to put that statement?

                 

                Thanks

                R

                • 5. Re: Invalid table name & column doesn't match format
                  3474044

                  Q_STEPHENSON,

                   

                  Also, I get the following error after I changed the sources of the date picker item from SQL QUERY and chose DATABASE COLUMN  for STATUS_DATE column:

                  ORA-01722: invalid number

                  • is_internal_error: false
                  • ora_sqlcode: -1722
                  • ora_sqlerrm: ORA-01722: invalid number
                  • component.type: APEX_APPLICATION_PAGE_PROCESS
                  • component.id: 52926329978143226
                  • component.name: Fetch Row from ALL_THREE_NEW_DATA
                  • error_backtrace:
                    ORA-06512: at line 1 ORA-06512: at "SYS.DBMS_SYS_SQL", line 2107 ORA-06512: at "SYS.WWV_DBMS_SQL", line 1033 ORA-06512: at "SYS.WWV_DBMS_SQL", line 1047 ORA-06512: at "APEX_050000.WWV_FLOW_DYNAMIC_EXEC", line 895 ORA-06512: at "APEX_050000.WWV_FLOW_DML", line 764 ORA-06512: at "APEX_050000.WWV_FLOW_PROCESS_NATIVE", line 526 ORA-06512: at "APEX_050000.WWV_FLOW_PROCESS_NATIVE", line 1106 ORA-06512: at "APEX_050000.WWV_FLOW_PLUGIN", line 2014 ORA-06512: at "APEX_050000.WWV_FLOW_PROCESS", line 188 
                  • error_statement:
                    begin begin  select "ROWID","ID","PRE_ID","SYSTEM","ML_VERSION",to_char("STATUS_DATE", :p$_format_mask1),"LOCATION","DATA_SYSTEM","PHYSICALHOST","PHYSVIRTUAL","DOMAINNAME","IPADDRESS","PURPOSE","DESCRIPTION","ATO","ATOAPP","OWNER","CMSOURCE","OS","HWMODEL","HARDWARECPU","NETWORK","CPUS","MEMORY","DISK","STATUS","NOTES","CREATE_DATE","DESTROY_DATE","SPLUNK","AUTO_DISCOVERY","BARCODE","IS_SSH","MONITORED","SCAN_DATE","CURRENT_BL","PT_IS_PUPPET","PT_PUPPETVAR_CLUSTER","PT_PUPPETVAR_ROLE","PT_PUPPETCLASS","PT_ENVIRONMENT","PT_PUPPETCLASSES","PT_PUPPETVARIABLES","ROOM","RACKNUM","STARTPOSITION","ENDPOSITION" into wwv_flow.g_column_values(1),wwv_flow.g_column_values(2),wwv_flow.g_column_values(3),wwv_flow.g_column_values(4),wwv_flow.g_column_values(5),wwv_flow.g_column_values(6),wwv_flow.g_column_values(7),wwv_flow.g_column_values(8),wwv_flow.g_column_values(9),wwv_flow.g_column_values(10),wwv_flow.g_column_values(11),wwv_flow.g_column_values(12),wwv_flow.g_column_values(13),wwv_flow.g_column_values(14),wwv_flow.g_column_values(15),wwv_flow.g_column_values(16),wwv_flow.g_column_values(17),wwv_flow.g_column_values(18),wwv_flow.g_column_values(19),wwv_flow.g_column_values(20),wwv_flow.g_column_values(21),wwv_flow.g_column_values(22),wwv_flow.g_column_values(23),wwv_flow.g_column_values(24),wwv_flow.g_column_values(25),wwv_flow.g_column_values(26),wwv_flow.g_column_values(27),wwv_flow.g_column_values(28),wwv_flow.g_column_values(29),wwv_flow.g_column_values(30),wwv_flow.g_column_values(31),wwv_flow.g_column_values(32),wwv_flow.g_column_values(33),wwv_flow.g_column_values(34),wwv_flow.g_column_values(35),wwv_flow.g_column_values(36),wwv_flow.g_column_values(37),wwv_flow.g_column_values(38),wwv_flow.g_column_values(39),wwv_flow.g_column_values(40),wwv_flow.g_column_values(41),wwv_flow.g_column_values(42),wwv_flow.g_column_values(43),wwv_flow.g_column_values(44),wwv_flow.g_column_values(45),wwv_flow.g_column_values(46),wwv_flow.g_column_values(47) from "TEST"."ALL_THREE_NEW_DATA" where "ROWID" = :p_rowid; end; end;

                  1.PNG   2.PNG    3.PNG    4.PNG

                  • 6. Re: Invalid table name & column doesn't match format
                    Mike Kutz

                    Do you really have a table called this?

                    "14/15/31_ALL_THREE"
                    

                     

                    Also, your SQL won't work.  You can't dynamically set a column name

                    SELECT STATUS_DATE to_char(systimestamp, 'DD-MON-YYYY HH24:MI:SS.FF') from dual
                    

                     

                    Not to mention, your subquery is not surrounded in quotes.

                     

                    The string "2016-11-09 15:16:24" is not in the same format as "YYYY-MON-DD HH24:MI:SS"

                    RTFM to find out what you did wrong:  https://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements004.htm#SQLRF00212

                     

                    MK

                    • 7. Re: Invalid table name & column doesn't match format
                      3474044

                       

                       

                       

                       

                       

                      • 8. Re: Invalid table name & column doesn't match format
                        Mike Kutz

                        What problem? -- to be more specific:  What is your business problem you are trying to solve?

                         

                        What website?

                         

                        MK

                        • 9. Re: Invalid table name & column doesn't match format
                          r.E.apex

                          So, I try to change the type of the column from Text Field to Date picker. But, my column's type is VARCHAR(30) and I think I need to change that to DATE to not get any error.

                           

                          What I have is a column with VARCHAR type and Text field for modifying the date.

                          What I want to do is to change the Text Field to Date Picker.

                           

                          Website: https://stackoverflow.com/questions/34329483/how-to-convert-date-to-timestampdd-mon-yyyy-hh24miss-ff-format-in-oracle/34…

                           

                          Thanks,

                          R

                          • 10. Re: Invalid table name & column doesn't match format
                            r.E.apex

                            Also, the another account is mine too. It is confusing, because I changed my email on the old account and had to create a new account. And now, the accounts' concept - name, my messages - is kin of glitching between the accounts.

                             

                            Thanks

                            • 11. Re: Invalid table name & column doesn't match format
                              r.E.apex

                              I tried the following, but got an error:

                              alter table ALL_THREE_NEW_DATA add (S_DATE date);

                              update ALL_THREE_NEW_DATA set S_DATE = to_date(STATUS_DATE, <<some format>>);

                              How can I solve the problem i stated in the threat?

                              • 12. Re: Invalid table name & column doesn't match format
                                Mike Kutz

                                r.E.apex wrote:

                                 

                                I tried the following, but got an error:

                                 

                                My car doesn't start.

                                 

                                You have given me about as much about your problem that I've given you about my car.

                                Please be aware that I am not looking over your shoulder.  I have no idea what error you saw.

                                 

                                (I really hope you ran those pieces of code in the SQL Workshop)

                                 

                                MK

                                • 13. Re: Invalid table name & column doesn't match format
                                  r.E.apex

                                  Hi Mike,

                                   

                                  I am sorry if you didn't see the attached screenshot. I uploaded that screenshot again. Please, let me know if you can see the error.

                                   

                                  Thanks.

                                   

                                  MY CODE:

                                  alter table ALL_THREE_NEW_DATA add (S_DATE date);

                                  update ALL_THREE_NEW_DATA set S_DATE = to_date(STATUS_DATE, <<some format>>);

                                  SCREENSHOT of ERROR (ORA-00936: missing expression):

                                  1.PNG

                                  • 14. Re: Invalid table name & column doesn't match format
                                    Mike Kutz

                                    r.E.apex wrote:

                                     

                                    Hi Mike,

                                     

                                    I am sorry if you didn't see the attached screenshot. I uploaded that screenshot again. Please, let me know if you can see the error.

                                     

                                    Thanks.

                                     

                                    MY CODE:

                                    alter table ALL_THREE_NEW_DATA add (S_DATE date);

                                    update ALL_THREE_NEW_DATA set S_DATE = to_date(STATUS_DATE, <<some format>>);

                                    SCREENSHOT of ERROR (ORA-00936: missing expression):

                                    1.PNG

                                     

                                    The highlighted part of your code is invalid SQL.

                                    The format that you use for <<some format>> needs to be a string that matches the format that is stored in the column STATUS_DATE.

                                     

                                    To discover what the valid values are for <<some format>>, go to the link I gave you earlier .. then... make a guess at what is should be for your data.

                                     

                                    I recommend that you play with your <<some format>> by doing a simple

                                    select to_char( sysdate, <<someformat>> ) from dual;

                                     

                                    This will give you an idea of what the input string should be for TO_DATE()

                                     

                                    I'm assuming you don't have Oracle XE installed locally.

                                    If that is the case feel free to try the select statement on:

                                    http://livesql.oracle.com

                                     

                                    MK

                                    1 2 Previous Next