Page 1 of 2 12 LastLast
Results 1 to 15 of 29

Thread: SQL*Plus

  1. #1
    Join Date
    Feb 2004
    Location
    hatfield
    Posts
    16

    SQL*Plus

    Hi I have to make a hotel booking data base as part of my university coursework.

    For one of the relations I have to store a time. When creating the relation, what data type would i set for the attribute. I'm really confused as what to do. Can anyone help?

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    Use DATE, it includes both date and time.

  3. #3
    Join Date
    Feb 2004
    Location
    hatfield
    Posts
    16
    - thanks for your help everyone, its really appreciated. I finally understand SQL, and actually found my self getting better at it!
    Last edited by sangeetailor; 03-08-2004 at 05:44 AM.

  4. #4
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    you have to provide date part, if not then you have to use to_date() function with time format so that Oracle will put default date value.

    insert into table (datecol)
    values (to_date('13:49','hh24:mi'));

  5. #5
    Join Date
    Feb 2004
    Location
    hatfield
    Posts
    16
    Hi tried what u said, I did the following:

    SQL> insert into test (time) values
    2 (to_date('13:49','hh24:mi'));

    1 row created.
    -------------

    But when i view the table, a date shows up.

    SQL> select * from test;

    TIME
    ---------
    01-FEB-04

    -----------

    I created the table with the following properties:

    SQL> create table test (
    2 time date);

    Table created.

  6. #6
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    That's how Oracle shows date value by default, you have to do to_date while you select also.

    select to_date(time, 'hh24:mi') from test;

  7. #7
    Join Date
    Mar 2003
    Location
    Woking, UK
    Posts
    152
    to exctract time part from date column use function to_char()

    select to_char(time, 'hh24:mi') from test;
    You Have To Be Happy With What You Have To Be Happy With (KC)

  8. #8
    Join Date
    Feb 2004
    Location
    hatfield
    Posts
    16
    - thanks for your help everyone, its really appreciated. I finally understand SQL, and actually found my self getting better at it!
    Last edited by sangeetailor; 03-08-2004 at 05:45 AM.

  9. #9
    Join Date
    Feb 2004
    Location
    hatfield
    Posts
    16
    YECKFOU, THE CODE YOU PROVIDED WORKS
    THANKS SO MUCH!

  10. #10
    Join Date
    Feb 2004
    Location
    hatfield
    Posts
    16
    - thanks for your help everyone, its really appreciated. I finally understand SQL, and actually found my self getting better at it!
    Last edited by sangeetailor; 03-08-2004 at 05:46 AM.

  11. #11
    Join Date
    Mar 2003
    Location
    Woking, UK
    Posts
    152
    No, for inserting use to_date()
    You Have To Be Happy With What You Have To Be Happy With (KC)

  12. #12
    Join Date
    Feb 2004
    Location
    hatfield
    Posts
    16
    ok thanks,

  13. #13
    Join Date
    Feb 2004
    Location
    hatfield
    Posts
    16
    I have the following table;

    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    RDATE DATE
    ROOM_NO VARCHAR2(2)
    SESSION_B VARCHAR2(9)
    BEVERAGE VARCHAR2(10)
    QTY NUMBER(3)
    TIME DATE


    If i wanted to do a select statement, to show all entries in the table, with the Time displaying as an actual time
    would I have to put all of the fields into the select statement?

    I've used the following:

    select rdate, room_no, session_b, beverage, qty, to_char(time, 'hh24:mi') from BEVERAGE_BOOKING;

    Is there any other way of performing this operation?
    Last edited by sangeetailor; 03-01-2004 at 10:43 AM.

  14. #14
    Join Date
    Feb 2004
    Location
    hatfield
    Posts
    16
    two tables with following properties:
    SQL> describe test;
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    TIME DATE
    PKTEST VARCHAR2(10)


    SQL> describe test2;
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    PKTEST NOT NULL VARCHAR2(10)
    PKNAME VARCHAR2(15)


    Im trying to create foreign keys references an attributes in another table, but i keep getting the following error:

    SQL> alter table test modify (
    2 pktest varchar2(10),
    3 foreign key (pktest) references test2);
    foreign key (pktest) references test2)
    *
    ERROR at line 3:
    ORA-02253: constraint specification not allowed here

  15. #15
    Join Date
    Mar 2003
    Location
    Woking, UK
    Posts
    152
    1. check if there's a PK defined on main table where you want to add FK

    ALTER TABLE test ADD CONSTRAINT test_pk PRIMARY KEY (pktest);

    2. to add FK you don't need to modify column, only add constraint

    ALTER TABLE test ADD CONSTRAINT test_test2_pktest_fk
    FOREIGN KEY (pktest) REFERENCES test2(pktest);
    You Have To Be Happy With What You Have To Be Happy With (KC)

Posting Permissions

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