Re: Create Table default Date Value to TO_CHAR

On 10 May 2002 04:23:33 -0700, biwombi_at_hotmail.com (Thiko) wrote:

>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

To display the time automatically
either
- use the to_char function
- issue alter session set nls_date_format = 'dd-mm-yyyy hh24:mi:ss' (or whatever is appropiate
- set NLS_DATE_FORMAT in hklm\software\oracle

You are trying to use a display function in a create table statement. This isn't appropiate syntax and doesn't make sense.

Regards

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Fri May 10 2002 - 06:37:34 CDT