Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2011
    Posts
    40

    Unanswered: How to change default sysdate's format for a table??

    Hi Everyone,

    I was trying to change (ALTER) a table's default column sysdate format?

    i want to display the date as April 20th, 2011.

    any help please?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    sysdate does NOT have any format; it is an internal datatype of DATE.

    SQL> select to_char(sysdate,'Month DD, YYYY') from dual;

    TO_CHAR(SYSDATE,'MONTHDD,YYYY')
    ---------------------------------------------
    April 17, 2011
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by arayici View Post
    i want to display the date as April 20th, 2011.
    Depends on the tool where you want to display it.
    In sqlplus either use TO_CHAR as demonstrated by anacedent or change the session's NLS_DATE_FORMAT.
    In SQL developer change the Date Format in NLS Parameters, available from menu via Tools -> Preferences -> Database.
    In TOAD, there is probably similar setting.
    In ... (sorry, I am out of ideas)

  4. #4
    Join Date
    Apr 2011
    Posts
    40
    Quote Originally Posted by anacedent View Post
    sysdate does NOT have any format; it is an internal datatype of DATE.

    SQL> select to_char(sysdate,'Month DD, YYYY') from dual;

    TO_CHAR(SYSDATE,'MONTHDD,YYYY')
    ---------------------------------------------
    April 17, 2011
    So we can't set the DEFAULT for column as SYSDATE to appear as character?

    we need to convert that data column (to_char) to character type when we want to display (select) it ?

    cannot create or alter the table with to_char option?

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Dates should be stored into DATE datatype columns, so no "to_char" is to be used. For the same reason, there's nothing you can "alter" there (as far as a TABLE is concerned).

    However, you might alter your session and set any format you wish.

    First, create a table with a DATE datatype column and insert a date value in there (sysdate will do):
    Code:
    SQL> create table test (col date);
    
    Table created.
    
    SQL> insert into test (col) values (sysdate);
    
    1 row created.
    You've already seen that TO_CHAR can be used to format the output:
    Code:
    SQL> select to_char(sysdate, 'mon dd yyyy') from test;
    
    TO_CHAR(SYSDATE,'MON
    --------------------
    apr 17 2011
    But ALTER SESSION can also be used; doing so, you don't need TO_CHAR any more:
    Code:
    SQL> alter session set nls_date_format = 'dd.mm.yyyy';
    
    Session altered.
    
    SQL> select col from test;
    
    COL
    ----------
    17.04.2011
    
    SQL> alter session set nls_date_format = 'yyyy mon day';
    
    Session altered.
    
    SQL> select col from test;
    
    COL
    ------------------
    2011 apr sunday

Posting Permissions

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