-
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?
-
Use DATE, it includes both date and time.
-
- 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.
-
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'));
-
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.
-
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;
-
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)
-
- 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.
-
YECKFOU, THE CODE YOU PROVIDED WORKS
THANKS SO MUCH!
-
- 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.
-
No, for inserting use to_date()
You Have To Be Happy With What You Have To Be Happy With (KC)
-
-
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.
-
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
-
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
-
Forum Rules
|
|