Re: Create Table default Date Value to TO_CHAR

Date columns store the full data information, including the time. When you set the default to SYSDATE, the full time value is stored. You would only lose the time component if you stored TRUNC(SYSDATE).

Sybrand had already explained how to get Oracle to display the time from a date column.

Good Luck!

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Chris Weiss
mailto:chris_at_hpdbe.com
www.hpdbe.com
High Performance Database Engineering
Available for long and short term contracts


"Thiko" <biwombi_at_hotmail.com> wrote in message
news:b8d3b40c.0205100323.b66f50d_at_posting.google.com...


> I've created a table Barf as follows:

>

> CREATE TABLE Barf (DBName Varchar2(15),UserName Varchar2(30),

> ConnectionDate DATE DEFAULT SYSDATE, UserCount NUMBER);

>

> Then inserted a row:

>

> INSERT INTO Barf (DBName,UserName,UserCount) VALUES

> ('ora.database','Development',234);

>

> Then selected * from table Barf:

>

> SELECT * FROM Barf;

>

> DBNAME          USERNAME                       CONNECTIO USERCOUNT

> --------------- ------------------------------ --------- ----------

> ora.database     Development                        10-MAY-02

> 234

>

> Then selected the individual columns so i could TO_CHAR on the DATE

> column ConnectionDate to give the time as well.

>

> SELECT DBName, UserName, TO_CHAR(ConnectionDate,'DD-MON-YYYY

> HH24:MI:SS'), UserCount FROM Barf;

>

> DBNAME          USERNAME                       TO_CHAR(CONNECTIONDA

> USERCOUNT

> --------------- ------------------------------ --------------------

> ----------

> ora.database     Development

> 10-MAY-200212:08:50   234

> 1 row selected.

>

> Fine.....

>

> Now, is it possible to use the CREATE TABLE statement to automatically

> convert the SYSDATE default value column to a TO_CHAR so when you do a

> select * the time is there as well?  Something along the lines of:

>

> CREATE TABLE Barf (DBName Varchar2(15),UserName Varchar2(30),

> ConnectionDate DATE DEFAULT TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'),

> UserCount NUMBER);

>

> ORA-01830: date format picture ends before converting entire input

> string