Results 1 to 7 of 7
  1. #1
    Join Date
    May 2005
    Posts
    54

    Unanswered: insert time into table

    I have a table having two columns with data type date.I want to insert date in first column and time in second column.I used the following insert statement and getting following error.

    INSERT INTO TADA VALUES (TO_DATE('07-Aug-2006','DD-MM-YYYY'),TO_DATE('18:03','HH:MM'));


    SQL Error: ORA-00932: inconsistent datatypes: expected NUMBER got DATE
    00932. 00000 - "inconsistent datatypes: expected %s got %s"
    *Cause:

    can anyone help me please.

    Thanks.

    Wasim

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Why two columns? DATE datatype contains both date and time. Additionally, you can not store only "time" into a DATE column, as you'd always have date along with time value.

    So - use one column and insert date and time together into the column:
    Code:
    SQL> insert into tada (date_and_time)
      2    values (to_date('07.08.2006 18:03', 'dd.mm.yyyy hh24:mi'));
    
    1 row created.
    If you, however, insist on separating date and time, you'll have to use another datatype (character or number), but such an approach will bring you problems in the future during "date" and "time" manipulation (sorting, date arithmetic, datatype conversions, ...). I'd strongly recommend you NOT to do that.

  3. #3
    Join Date
    May 2005
    Posts
    54
    Thanks littlefoot for reply.

    I used your suggestion but now a problem is how to insert 'second' part of date.
    I used following statement but when i looked table there is only date rather the time. or it may be full date and time in column but only date part is visible.

    INSERT INTO TADA VALUES ('1536',TO_DATE('09-Jul-2006 00:00','DD-MM-YYYY hh24:mi'),TO_DATE('30-jul-2006 17:40','dd-mm-yyyy hh24:mi'),TO_DATE('11-Jul-2006 18:40','DD-MM-YYYY hh24:mi'),TO_DATE('30-jul-2008 18:35','dd-mm-yyyy hh24:mi'),'Karachi','Enquiry Self',8300,'HR Department',TO_DATE('21-Aug-2006','DD-MM-YYYY'));

    Thanks.

    Wasim

  4. #4
    Join Date
    Aug 2004
    Location
    Rome, Italy
    Posts
    81
    can you tell me what do you mean to have the time separate?

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Time is there; you just don't see it - alter session (or ask your DBA to do that):
    Code:
    SQL> select * from tada;
    
    DATE_AND
    --------
    07.08.06
    
    SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';
    
    Session altered.
    
    SQL> select * from tada;
    
    DATE_AND_TIME
    -------------------
    07.08.2006 18:03:00
    
    SQL>

  6. #6
    Join Date
    May 2005
    Posts
    54
    Thank you littlefoot

    I got result. Actually I'm totally new in ORACLE. I hope we will meet in future.

    As a general knowledge how we can create hypertext in ORACLE forms. As I'm using oracle ERP suit in which this facility is available.
    I'm designing a form in ORACLE 9i and wants to draw like ERP.

    Thanks.

    Wasim

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Hyperlink in Forms? I have never used them, but - you might research use of the WEB.SHOW_DOCUMENT built-in; there's an example of how to display the URL in the target window. Also, I've read that one could use Java beans (check this OraFAQ Forum thread); it is said that the example is available at Oracle Metalink.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •