13 Replies Latest reply: Jul 9, 2013 5:53 AM by Ashu_Neo RSS

    Concatinate date and time collumns

    994543

      Hello,

       

      I have a problem.

       

      In forms 6i i have two non database columns:

       

      1st is Date format (dd.mm.yyyy)

      2nd is Hour format (HH24:MI:SS)

       

      Now i want to combine those two into 3rd fild which is date/time format (dd.mm.yyyy HH24:MI:SS).

       

      I have tried this aproach:

       

      select to_date(to_char(:field1,'dd.mm.yyyy') || to_char(:field2,'HH24:MI:SS') from dual;

       

      Ora: 01850

       

      Plus i have also tried various other optios i found on net but all i get are variations of ora 01850 errors.

       

      Please help!

       

      Thank you!

        • 1. Re: Concatinate date and time collumns
          Manik

          I tried this way and worked :

           

          select to_date(:field1||' '||:field2,'dd.mm.yyyy HH24:MI:SS') from dual ;

           

           

          Cheers,

          Manik

           

          • 2. Re: Concatinate date and time collumns
            Karthick_Arp

            994543 wrote:

             

            Hello,

             

            I have a problem.

             

            In forms 6i i have two non database columns:

             

            1st is Date format (dd.mm.yyyy)

            2nd is Hour format (HH24:MI:SS)

             

            Now i want to combine those two into 3rd fild which is date/time format (dd.mm.yyyy HH24:MI:SS).

             

            I have tried this aproach:

             

            select to_date(to_char(:field1,'dd.mm.yyyy') || to_char(:field2,'HH24:MI:SS') from dual;

             

            Ora: 01850

             

            Plus i have also tried various other optios i found on net but all i get are variations of ora 01850 errors.

             

            Please help!

             

            Thank you!

             

            From your question what i understand is that FIELD1 and FIELD2 are already character values. So there is no point in using TO_CHAR function on them. What you need to do is concat the two value and then use the TO_DATE function by specifying the format string. Something like

             

            TO_DATE(FIELD1 || ' ' || FILED2, 'DD.MM.YYYY HH24:MI:SS')

             

            To get a good understanding about the datatype DATE, how it is stored and displayed please read Re: 15. I want to store Dates in the Format...

            • 3. Re: Concatinate date and time collumns
              994543

              Both answers still gives error:  ora 01858

               

              Is there another way ... i mean i dont event know what else to post here to help solve this issue.

               

              where ever i looked for solution answer was almost identical to what u guys posted here.

              • 4. Re: Concatinate date and time collumns
                Animesh Tripathi

                Hi,

                 

                You can try the below statement and frame it your need.

                 

                select to_char(to_date(sysdate),'dd.mm.yyyy')||'  '||to_char(to_date('21:08:54','HH24:MI:SS'),'HH24:MI:SS') from dual;

                 

                 

                Thanks
                Animesh

                • 5. Re: Concatinate date and time collumns
                  Manik

                  Can you paste exactly (by this I mean check if you have any spaces etc....) what input you are trying to pass to the query?

                   

                  Cheers,

                  Manik.

                  • 6. Re: Concatinate date and time collumns
                    Karthick_Arp

                    994543 wrote:

                     

                    Both answers still gives error:  ora 01858

                     

                    Is there another way ... i mean i dont event know what else to post here to help solve this issue.

                     

                    where ever i looked for solution answer was almost identical to what u guys posted here.

                     

                    You need to post the values of FIELD1 and FIELD2. If they contain the values in the format you specified then the solution should work without any flaw. here is an example

                     

                    SQL> var field1 varchar2(10)

                    SQL> var field2 varchar2(8)

                    SQL> exec :field1 := '30-10-2013'

                     

                    PL/SQL procedure successfully completed.

                     

                    SQL> exec :field2 := '23:59:59'

                     

                    PL/SQL procedure successfully completed.

                     

                    SQL> alter session set nls_date_format = 'DD.MM.YYYY HH24:MI:SS';

                     

                    Session altered.

                     

                    SQL> select to_date(:field1 || ' ' || :field2, 'DD.MM.YYYY HH24:MI:SS') dt from dual;

                     

                    DT
                    -------------------
                    30.10.2013 23:59:59

                     

                    SQL>

                    • 7. Re: Concatinate date and time collumns
                      Karthick_Arp

                      AnimeshTripathi wrote:

                       

                      Hi,

                       

                      You can try the below statement and frame it your need.

                       

                      select to_char(to_date(sysdate),'dd.mm.yyyy')||'  '||to_char(to_date('21:08:54','HH24:MI:SS'),'HH24:MI:SS') from dual;

                       

                       

                      Thanks
                      Animesh

                       

                      I assume you don't have basic understand about DATE datatype. Otherwise you will not be trying to convert SYSDATE to date using TO_DATE. Please read the link posted by me to learn about date datatype.

                      • 8. Re: Concatinate date and time collumns
                        Ashu_Neo

                        Are you asking for this? (Assuming date format and hr format columns are date type).

                        SQL> with dat as
                          2  (
                          3  select to_date('09.07.2013','dd.mm.yyyy') date_col, to_date('13:00:00','hh24:mi:ss') hr_col from dual
                          4  )
                          5  select
                          6    to_char(date_col,'dd.mm.yyyy')||' '||to_char(hr_col,'hh24:mi:ss') res
                          7  from dat
                          8  /

                        RES
                        -------------------
                        09.07.2013 13:00:00

                         

                        Message was edited by: Ashu_Neo : Changed temp table(with clause) values from string type to date type.

                        • 9. Re: Concatinate date and time collumns
                          Karthick_Arp

                          Ashu_Neo wrote:

                           

                          Are you asking for this? (Assuming date format and hr format columns are date type)

                          SQL> with dat as
                            2  (
                            3  select '09.07.2013' date_col, '13:00:00' hr_col from dual
                            4  )
                            5  select to_char(to_date(date_col, 'dd.mm.yyyy'),'dd.mm.yyyy')||' '||to_char(to_date(hr_col,'hh24
                          :mi:ss'),'hh24:mi:ss') res
                            6  from dat
                            7  /

                          RES
                          -------------------
                          09.07.2013 13:00:00

                          SQL>

                           

                          Seriously?

                           

                          Lets see Your code. DATE_COL and HR_COL are defined as STRING in your DAT table (WITH clause). Now what are you doing

                           

                          to_char(

                                     to_date

                                     (

                                         date_col, 'dd.mm.yyyy'

                                     ),'dd.mm.yyyy'

                                 )

                           

                          1. Convert String to Date

                          2. convert Date to String

                           

                          Why? Was it not already a STRING?

                          • 10. Re: Concatinate date and time collumns
                            Marwim

                            994543 wrote:

                             

                            Both answers still gives error:  ora 01858

                             

                            Is there another way ... i mean i dont event know what else to post here to help solve this issue.

                             

                            where ever i looked for solution answer was almost identical to what u guys posted here.

                            Most likely your time column has invalid data. Check the data for invalid hours/minutes/seconds.

                            Examples can be found here:

                            how to select valid dates only

                            Re: Handle Date conversion error without function

                            Re: check if the data stored in a varchar2 type column is date or not

                             

                            Regards

                            Marcus

                            • 11. Re: Concatinate date and time collumns
                              Animesh Tripathi

                              Hi

                              • 12. Re: Concatinate date and time collumns
                                Karthick_Arp


                                The point is not about SYSDATE or a DATE column from table. Using TO_DATE on a date datatype does not make sense.

                                • 13. Re: Concatinate date and time collumns
                                  Ashu_Neo

                                  Karthick_Arp wrote:

                                  Seriously?

                                  Yes. It's a serious mistake with slip of my mind. Please notify me if you find anywhere something more or less other than this. I reverted it in my earlier post.

                                  Thank you.