date type data length show at times 7 and at times 8
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: date type data length show at times 7 and at times 8

  1. #1
    Join Date
    Aug 2003
    Posts
    1

    date type data length show at times 7 and at times 8

    PL see the following I am not able to under stand

    step1 --------------
    select data_length from user_tab_columns where
    data_type='DATE';

    DATA_LENGTH
    -----------
    7

    step2 --------------

    create table xyz
    (a date);

    Table created

    step3 --------------

    insert into xyz values (to_date('10-aug-03'))

    1 row created

    step4 --------------

    SQL> select distinct(data_length) from user_tab_columns where
    2 data_type='DATE';

    DATA_LENGTH
    -----------
    7

    step5 --------------

    create or replace view temp as (Select trunc(A) as a from xyz);

    view created

    step 6
    SQL> select distinct(data_length) from user_tab_columns where
    2 data_type='DATE';

    DATA_LENGTH
    -----------
    7
    8
    step 7

    select TABLE_NAME,COLUMN_NAME
    from user_tab_columns where
    data_type='DATE' and data_length=8;


    The data length for date column shows 8 when we use truc(date_column) in a view..
    As oracle uses 7 bytes to store a date type
    usage
    century year month date hour minute second

    pls guide where has the length increased to 8..

    regards,
    Ashwani

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    The DATA_LENGTH column in USER_/ALL_/DBA_TAB_COLUMNS is more or less meaningless for views - in particular for DATE datatype. It doesn't tell you anything since view is only a stored query.

    FYI, as soon as you perform any DATE operation on DATE datatype in view (apply any kind of DATE functions, perform any date arythmetics) the DATA_LENGTH will show 8 bytes - so it's not only with TRUNC() function.

    Abyway, to understand where that 8th byte came from, check the following thread (in particular on page 2): http://www.dbasupport.com/forums/sho...threadid=16402
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

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