sysdate and userid
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: sysdate and userid

  1. #1
    Join Date
    May 2006
    Posts
    4

    sysdate and userid

    Hello,

    I have an application that needs to have

    1. An auto generated timestamp in a field (created_date)
    2. An auto generated field for the user who made the
    modifications to the table (created_by)


    I come from the sql server world, where getdate(), suser_id() are the way to get the datetime value and the user.

    I've created a default on the table column as sysdate, but that only seems to be giving me the date portion and not the time.

    Also, is there an equivalent function to return the schema owner that can be placed as a defualt in a create table statement

    Thanks in advance

  2. #2
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Hello,
    you did not mention the database version. Suppose it's 10g:

    SQL> create table test
    2 (
    3 dt date default sysdate not null,
    4 ts timestamp default systimestamp not null,
    5 usr number default uid not null
    6 )
    7 /

    Table created.

    SQL> insert into test values(default, default, default)
    2 /

    1 row created.

    SQL> select * from test;

    DT TS USR
    ---------- ------------------------------------------ ----------
    2006-05-18 18-MAY-06 08.29.46.759321 PM 93

    SQL> alter session set nls_date_format='yyyy.mm.dd hh24:mi:ss';

    Session altered.

    SQL> select * from test;

    DT TS USR
    ------------------- ------------------------------------------ ----------
    2006.05.18 20:29:46 18-MAY-06 08.29.46.759321 PM 93
    Ales
    The whole difference between a little boy and an adult man is the price of toys

  3. #3
    Join Date
    May 2006
    Posts
    4
    thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width