Results 1 to 8 of 8

Thread: Default Date

  1. #1
    Join Date
    Feb 2003
    Posts
    44

    Wink Unanswered: Default Date

    Hi, I'm new to Oracle . . . how do I set a default date for when a new record is entered into a table?

    date_entered DATE default SYSDATE


    If so - it seems to work however when I do a select statement the field is blank. If the date is there how do I display it?
    Last edited by jameselmer; 02-08-04 at 15:41.
    James Elmer
    Clinical Programmer Analyst
    Wellstat Biologics Corporation
    800.227.0582 x3338
    jelmer@wellstatbiologics.com

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    create table a
    (
    date_field DATE default SYSDATE
    );

    insert into a values(default);

    OR

    create table a
    (
    var VARCHAR2(10),
    date_field DATE default SYSDATE
    );

    insert into a (var) values ('x');
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Feb 2003
    Posts
    44
    ok so I did that and when I queried by select * from table the date_field was still blank or didn't show anything. Does that mean it didn't work or I need to force it to show correctly?
    James Elmer
    Clinical Programmer Analyst
    Wellstat Biologics Corporation
    800.227.0582 x3338
    jelmer@wellstatbiologics.com

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    If you will actually show us EXACTLY what you did
    and EXACTLY what happened, maybe somebody can
    answer your question(s).
    Just claiming that "it doesn't work", is less than helpful.
    We are NOT mind readers.

  5. #5
    Join Date
    Feb 2003
    Posts
    44
    please don't be a jerk.
    James Elmer
    Clinical Programmer Analyst
    Wellstat Biologics Corporation
    800.227.0582 x3338
    jelmer@wellstatbiologics.com

  6. #6
    Join Date
    Feb 2003
    Posts
    44
    Originally posted by r123456
    create table a
    (
    date_field DATE default SYSDATE
    );

    insert into a values(default);

    OR

    create table a
    (
    var VARCHAR2(10),
    date_field DATE default SYSDATE
    );

    insert into a (var) values ('x');

    Hey thanks for being helpful . . . my insert statement was the problem as I left out the word default. Would make more sense if Oracle filled in the default value if the input was null.
    James Elmer
    Clinical Programmer Analyst
    Wellstat Biologics Corporation
    800.227.0582 x3338
    jelmer@wellstatbiologics.com

  7. #7
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Null refers to the value being unknown or not applicable.

    Your requirement can easily be solved through a PL/SQL trigger that replaces any instance of null for a given column with the column default, if specified.
    Last edited by r123456; 02-08-04 at 23:09.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  8. #8
    Join Date
    Feb 2003
    Posts
    44
    Originally posted by r123456
    Null refers to the value being unknown or not applicable.

    Your requirement can easily be solved through a PL/SQL trigger that replaces any instance of null for a given column with the column default, if specified.

    Good point - I used the wrong term - I was just thinking that if the field was left blank and there was already a default value set up in the table description that it would make sense to automatically fill it in.

    I haven't quite gotten to PL/SQL but it sounds fun. Thanks again for the help.
    James Elmer
    Clinical Programmer Analyst
    Wellstat Biologics Corporation
    800.227.0582 x3338
    jelmer@wellstatbiologics.com

Posting Permissions

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