This discussion is archived
13 Replies Latest reply: Jul 9, 2013 3:53 AM by Ashu_Neo RSS

Concatinate date and time collumns

994543 Newbie
Currently Being Moderated

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 Expert
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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
    AnimeshTripathi Newbie
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Pro
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

    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
    AnimeshTripathi Newbie
    Currently Being Moderated

    Hi

  • 12. Re: Concatinate date and time collumns
    Karthick_Arp Guru
    Currently Being Moderated


    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 Pro
    Currently Being Moderated

    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points