Re: Times and dates in Oracle

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 03 Aug 1999 17:08:06 GMT
Message-ID: <37b4210c.14104901@newshost.us.oracle.com>

A copy of this was sent to "Eamonn Keating" <oristech_at_tinet.ie> (if that email address didn't require changing) On Tue, 3 Aug 1999 17:34:16 +0100, you wrote:

>I want to write a script to create a table which includes a date and time
>field. How can this be done in oracle. When I do the following :
>
>CREATE TABLE SPECAUD
> PARTCODE VARCHAR2(12) ,
> CHANGE_DATE DATE,
>...
>CONSTRAINT SPECAUD_PRIMARY_KEY PRIMARY KEY ( PARTCODE,CHANGE_DATE,REV_NO));
>
>it will not allow me to enter a time value with the date field.
>Also is it possible to create a field holding only the time
>Any help in this regard would be greatly appreciated.
>Eamonn
>

insert into specaud ( change_date )
values ( to_date( '01-aug-1999 14:02:33', 'dd-mon-yyyy hh24:mi:ss' ) );

is but one way to do it -- you have to specify a date mask used to translate your string.

there is no datatype to hold just a time. If i wanted to put 5:02pm into a database all by itself i would probably put 17*(1/24)+2*(1/24/60) in a NUMBER field. That way, I could then easily add that field to a date. for example:

SQL> select trunc(sysdate)+ ( 17*(1/24)+2*(1/24/60) ) from dual;

TRUNC(SYSDATE)+(17*(

---

03-aug-1999 17:02:00

so I would use a number and store it as HOURS*(1/24)+MINUTES*(1/24/60)+SECONDS*(1/24/60/60) --
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Aug 03 1999 - 12:08:06 CDT