0

Umm, Hello, can someone tell me how many bytes takes Date Time format in Oracle SQL pls ?

I tryed to find it with google, but i couldnt find it. Thank for answer.

Mirek Mareš
  • 107
  • 1
  • 4
  • 10
  • I just Googled ["date time oracle storage bytes"](https://www.google.co.uk/webhp?sourceid=chrome-instant&rlz=1C1CHWA_enGB616GB616&ion=1&espv=2&ie=UTF-8#q=date+time+oracle+storage+bytes) and the [first link](https://docs.oracle.com/cd/E17952_01/refman-5.1-en/storage-requirements.html) gives all the answers you need. Not really sure how you couldn't find it. – DavidG Apr 23 '15 at 12:39
  • @DavidG Your "first link" is **NOT** the official **Oracle Documentation**, it is **MySQL 5.1 Reference Manual**. This happens with many folks. However, I must say that it is not a difficult task to find the answer with a google search. – Lalit Kumar B Apr 23 '15 at 13:52
  • 1
    @Mirek See my answer here http://stackoverflow.com/a/26652945/3989608 – Lalit Kumar B Apr 23 '15 at 13:58
  • @LalitKumarB you missed there 8 byte `Type 13` Oracle runtime DATE – Ilia Maskov Apr 23 '15 at 14:05
  • @agent5566 No, not at all. I haven't missed anything. I have contributed a lot to improvise the Oracle documentation on DATEs and literals. Do not confuse with **fractional seconds or a time zone**. The size is fixed at **7 bytes** with elements `YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND`. See http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements001.htm#i54330 – Lalit Kumar B Apr 23 '15 at 14:13
  • @LalitKumarB Good spot! Though my point still stands, this information is simple fo look up! – DavidG Apr 23 '15 at 14:14
  • @agent5566, Appreciate your involvement here. +1 and good going mate! – Lalit Kumar B Apr 23 '15 at 14:16
  • @LalitKumarB fixed **7 bytes** for storage data files, but date exressions in runtime is **8 byte**. http://www.ixora.com.au/notes/date_representation.htm. But as DavidG have said, it pretty easy to google ;) – Ilia Maskov Apr 23 '15 at 14:21
  • Firstly, I got confused with the comments from DavidG and agent5566. David ,the incorrect link was for you, rest regarding the date storage is for @agent5566. – Lalit Kumar B Apr 23 '15 at 14:39
  • Possible duplicate of [How are dates stored in Oracle?](http://stackoverflow.com/questions/13568193/how-are-dates-stored-in-oracle) – MT0 Apr 08 '16 at 08:54

2 Answers2

4

You can use DUMP function to retrive information about expression value, like this SELECT DUMP(SYSDATE,10) FROM dual and it says Typ=13 Len=8: 223,7,4,23,14,17,41,0, so 8 bytes. From oracle docs http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions048.htm#SQLRF00635

DUMP returns a VARCHAR2 value containing the datatype code, length in bytes, and internal representation of expr. The returned result is always in the database character set.

Aslo, there are two different raw forms for DATEs in Oracle: https://community.oracle.com/thread/2257401

  1. Type 12 (7 bytes) is used for DATE columns

  2. Type 13 (8 bytes) is used for other DATE expressions, including DATE literals and results for date arithmetic and functions.

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
Ilia Maskov
  • 1,858
  • 16
  • 26
1

The size of a date time format is fixed at 7 bytes, see Oracle's documentation or alternatively run this:

select vsize(sysdate) from dual

or this:

create table test (dt date);

select data_length from user_tab_columns where table_name = 'ZTEST';
Alex
  • 21,273
  • 10
  • 61
  • 73