Home » SQL & PL/SQL » SQL & PL/SQL » Why So ..????
Why So ..???? [message #10380] Mon, 19 January 2004 06:33 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: Implementing Average function in PL/SQL
Next Topic: Trigger - Check on the same table!
Goto Forum:
  


Current Time: Thu Mar 28 18:55:50 CDT 2024