How to create a time field in Oracle 1

[mrasad]

Hi

I want to create a field for 'start time'. I have written Time_start date not null .... but when I try to enter a time in the table, it say incorrect use (of something).

Is it possible to have a time field without the :SS, so its just HH:MM?

Asad

 

[lewisp]

When you save the time to your date column, use

[tt]To_Date('14:55','HH24:MI');[/tt]

When you select the time from the column, use

[tt]SELECT To_Char(date_column,'HH24:MI').... [/tt]

 

[mrasad]

Thank you.

LEts say I wanted to create a table called member. The member table would have memberNO (PK) member name, member jonied date and member join time;

how do i create the table, insert the values and select the the date and time fields.

------
my attempt, but it not work ...

CREATE TABLE MEMBER
(MemberID number(7) not null primary key,
Member_name varchar(15) not null, Date_joined date ('DD:MMM:YY'),
Time_joined date ('HH24:MI');

 

[SantaMufasa]

MrAsad,

To add to Lewis's excellent suggestion, I just want to clarify that when you store time-only information in an Oracle "time-related" column, Oracle still consumes the same number of bytes for just the time as it would for both time and date. A standard Oracle DATE column, which always includes allocation for TIME as well, the length is 7 bytes. The 7 bytes allocate as follows:

1 byte for two-digit Century
1 byte for two-digit Year
1 byte for two-digit Month
1 byte for two-digit Day
1 byte for two-digit Hour
1 byte for two-digit Minute
1 byte for two-digit Second
If the value is negative, it is B.C.; if the value is positive, it is A.D. The range of a DATE column (regardless of what anyone's documentation says) is:

4713-12-31 23:59:59.50000000000000001 B.C. to
9999-12-31 23:59:59.49999999999999... A.D.

In Oracle 9i, you can store sub-second granularity if you use Oracle's TIMESTAMP datatype. This datatype uses 11 bytes of storage: same format as DATE, plus 4 bytes for milliseconds.

So, in direct answer to your question, MrAsad, "...how do i create the table (MEMBER table with date and time joined), insert the values and select the the date and time fields, here is the best scenario, in my opinion:

SQL> create sequence member_seq;

Sequence created.

SQL> CREATE TABLE MEMBER
  2   (MemberID number(7) primary key -- NOT NULL is automatic w/Primary Key
  3   ,Member_name varchar(15) not null
  4   ,Date_joined date
  5   );

Table created.

SQL> insert into member values (member_seq.nextval,'MrAsad',sysdate);

1 row created.

SQL> col a heading "Date/Time Joined" format a22
SQL> select memberID, member_name, to_char(date_joined,'yyyy-mm-dd hh24:mi:ss')a
  2   from member;

  MEMBERID MEMBER_NAME     Date/Time Joined
---------- --------------- ----------------------
         1 MrAsad          2003-12-26 12:50:44

1 row selected.

Mufasa
(aka Dave of Sandy, Utah, USA @ 19:52 (26Dec03) GMT, 12:52 (26Dec03) Mountain Time)

 

[mrasad]

Thanks mate. Question. Is sysdate short for system date?

Would it be possible for me to insert a time, for example a time a room has been booked for? when you write sysdate does that display the system date?

Thanks for your help

 

[SantaMufasa]

MrAsad,

Q1: "Would it be possible for me to insert a time, for example a time a room has been booked for?"

A1: Certainly. Here is sample code:

SQL> create sequence RoomBooking_seq;

Sequence created.

SQL> create table RoomBookings
  2   (Booking_ID  number
  3   ,Bldg_id  varchar2(50)
  4   ,Room_Num  varchar2(20)
  5   ,Reservation_Beg date
  6   ,Reservation_End date
  7   ,Reserver_Name  varchar2(20)
  8   ,Reserver_Phone  varchar2(20)
  9   );

Table created.

SQL> insert into RoomBookings values
  2   (RoomBooking_seq.nextval
  3   ,'Jesse Knight Bldg.'
  4   ,'A-105'
  5   ,to_date('2004-01-05 10:00:00','YYYY-MM-DD HH24:MI:SS')
  6   ,to_date('2004-01-05 14:00:00','YYYY-MM-DD HH24:MI:SS')
  7   ,'Dave Hunt'
  8   ,'801-733-5333'
  9   )
 10  /

1 row created.

SQL> col a heading "Reserved|Room" format a25
SQL> col b heading "Reservation|Start" format a12
SQL> col c heading "Reservation|End" format a12
SQL> col d heading "Reservation|Contact|Info" format a25
SQL> select Room_num||', '||Bldg_id a
  2   ,to_char(Reservation_Beg,'hh:mi a.m., DD Mon, YYYY') b
  3   ,to_char(Reservation_End,'hh:mi a.m., DD Mon, YYYY') c
  4   ,Reserver_Phone||': '||Reserver_Name d
  5  from RoomBookings;

                                                    Reservation
Reserved                  Reservation  Reservation  Contact
Room                      Start        End          Info
------------------------- ------------ ------------ -----------------------
A-105, Jesse Knight Bldg. 10:00 a.m.,  02:00 p.m.,  801-733-5333: Dave Hunt
                          05 Jan, 2004 05 Jan, 2004


1 row selected.

Q2: "when you write sysdate does that display the system date?"

A2: Yes.

Cheers,

Mufasa
(aka Dave of Sandy, Utah, USA @ 03:05 (27Dec03) GMT, 20:05 (26Dec03) Mountain Time)

 

[mrasad]

You are soooooooooooooooooooooo clever, thank you so so much.

 

[mrasad]

How do I create a space between two fields when I join them.

Example, I want to join forename and surname into one field when a select statement is run.

I've done this;

select memberID, forename || Surname b, ....

and I get the 2 fields into one. However their is no space between them.

 

[SantaMufasa]

So, you concatenate a space, as well:

select memberID, forename||' '||surname b,...

Mufasa
(aka Dave of Sandy, Utah, USA @ 19:45 (28Dec03) GMT, 12:45 (28Dec03) Mountain Time)

 

[mrasad]

Thank you again ....

 

[DeepDiverMom]

Asad,
I noticed that santamufasa kindly solved 3 of your issues on this one thread alone and you said &quot;Thanks&quot;, but the method on TekTips for saying &quot;Thank You&quot; for particuallry helpful posts is to click &quot;Thank <poster> for this valuable post!&quot; At the end of each reply.

I'll click it for you this time on this thread since I benefited from the posts as well - but I'll leave it up to you to go back to your other threads and give an appropriate TekTips &quot;Thanks&quot; where necessary.

 

[mrasad]

Sorry mate. I'll remember for next time.

Asad