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

[Ashwani]

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

[jmodic]

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