Home » SQL & PL/SQL » SQL & PL/SQL » Why So ..????
Why So ..???? [message #10380] |
Mon, 19 January 2004 06:33 |
Rishi
Messages: 63 Registered: January 2001
|
Member |
|
|
SQL> update emp set hiredate=sysdate where ename like 'KIN%';
1 row updated.
SQL> select hiredate,sysdate from emp where ename like 'KIN%';
HIREDATE SYSDATE
--------- ---------
19-JAN-04 19-JAN-04
SQL> select vsize(hiredate),vsize(sysdate) from emp where ename like 'KIN%';
VSIZE(HIREDATE) VSIZE(SYSDATE)
--------------- --------------
7 8
|
|
|
Re: Why So ..???? [message #10381 is a reply to message #10380] |
Mon, 19 January 2004 11:43 |
Thiru
Messages: 1089 Registered: May 2002
|
Senior Member |
|
|
thats becos HIREDATE is a stored date datatype and SYSDATE is a function that returns the date from the memory(ie its not stored in the database) and hence their datatypes are 'little' different.
Look here :
thiru@9.2.0:SQL>update t set hiredate=sysdate where ename like 'Ward%';
1 row updated.
thiru@9.2.0:SQL>select hiredate,sysdate from t where ename like 'Ward%';
HIREDATE SYSDATE
--------- ---------
19-JAN-04 19-JAN-04
thiru@9.2.0:SQL>select vsize(hiredate),vsize(sysdate) from t where ename like 'Ward%';
VSIZE(HIREDATE) VSIZE(SYSDATE)
--------------- --------------
7 8
thiru@9.2.0:SQL>SELECT VSIZE(HIREDATE) FROM T;
VSIZE(HIREDATE)
---------------
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
15 rows selected.
thiru@9.2.0:SQL>SELECT VSIZE(SYSDATE) FROM DUAL;
VSIZE(SYSDATE)
--------------
8
thiru@9.2.0:SQL>select dump(hiredate) from t ;
DUMP(HIREDATE)
----------------------------------------------------------------------
<B>Typ=12 Len=7:</B> 119,180,12,17,1,1,1
Typ=12 Len=7: 119,181,2,20,1,1,1
Typ=12 Len=7: 120,104,1,19,17,30,47
Typ=12 Len=7: 119,181,4,2,1,1,1
Typ=12 Len=7: 119,181,9,28,1,1,1
Typ=12 Len=7: 119,181,5,1,1,1,1
Typ=12 Len=7: 119,181,6,9,1,1,1
Typ=12 Len=7: 119,187,4,19,1,1,1
Typ=12 Len=7: 119,181,11,17,1,1,1
Typ=12 Len=7: 119,181,9,8,1,1,1
Typ=12 Len=7: 119,187,5,23,1,1,1
Typ=12 Len=7: 119,181,12,3,1,1,1
Typ=12 Len=7: 119,181,12,3,1,1,1
Typ=12 Len=7: 119,182,1,23,1,1,1
Typ=12 Len=7: 120,103,5,20,22,37,1
15 rows selected.
thiru@9.2.0:SQL>select dump(sysdate) from dual;
DUMP(SYSDATE)
----------------------------------------------------------------------
<B>Typ=13 Len=8:</B> 212,7,1,19,16,39,8,0
HIREDATE is Data Type# 12 while Sysdate is type # 13.
One is from your database. The other is derived from the OS and goes through Memory . The internal representations are little differrent.
-Thiru
|
|
|
Re: Why So ..???? [message #10382 is a reply to message #10381] |
Mon, 19 January 2004 17:50 |
Rishi
Messages: 63 Registered: January 2001
|
Member |
|
|
Thanks a Lot Thiru.. Nice Job Done...
But Thiru I am a newbie to Oracle and have seen this Dump() fuction for the first time.
Could u explain a bit in detail for it and whats that 191,234,???? number in front of Date Columns . Does these represent number of Days since that 31 Dec BC Year...????
Looking forward for ur reply...
Thanks again..
|
|
|
Re: Why So ..???? [message #10387 is a reply to message #10382] |
Tue, 20 January 2004 06:35 |
Thiru
Messages: 1089 Registered: May 2002
|
Senior Member |
|
|
Rishi,
Lets see ..
SQL> select to_char(hiredate,'YYYY MM DD HH MI SS') from t where ename='SMITH';
TO_CHAR(HIREDATE,'Y
-------------------
1980 12 17 12 00 00
SQL> select dump(hiredate) from t where ename='SMITH';
DUMP(HIREDATE)
--------------------------------------------------------------------------------
Typ=12 Len=7: 119,180,12,17,1,1,1
In here, we have 7 bytes.
Byte 1 = Century ( 19)
Byte 2 = Year ( 80 )
Byte 3 = Month ( 12)
Byte 4 = Date ( 17 )
Byte 5 = Hour ( 1)
Byte 6 = Minute ( 1 )
Byte 7 = Seconds ( 1 )
Bytes 5, 6 , 7 ( the time part) have an 1 ,maybe to indicate the Zero value.
-- Lets update the time part to be 01 01 01 and see whats the internal representation is
SQL> update t set hiredate=to_date('1980 12 17 01 01 01','YYYY MM DD HH MI SS') where ename='SMITH';
1 row updated.
SQL> select to_char(hiredate,'YYYY MM DD HH MI SS') from t where ename='SMITH';
TO_CHAR(HIREDATE,'Y
-------------------
1980 12 17 01 01 01
SQL> select dump(hiredate) from t where ename='SMITH';
DUMP(HIREDATE)
--------------------------------------------------------------------------------
Typ=12 Len=7: 119,180,12,17,2,2,2
-- as you see here, 2 ,2 ,2 is used to represent HH(01),MI(01),SS(01)
-Thiru
|
|
|
Goto Forum:
Current Time: Thu Mar 28 18:55:50 CDT 2024
|