Posted by Richard Foote has written a post about not using the DATE datatype for storing dates.
So I’ve come up with a revolutionary system to store dates as well…using the very efficient RAW datatype.
Here’s a demo
SQL> create table t ( x raw(7) );
Table created.
SQL>
SQL> create or replace
2 procedure store_date(p_yyyymmddhh24miss varchar2) is
3 begin
4 insert into t
5 values
6 (
7 hextoraw(
8 to_char(to_number(substr(p_yyyymmddhh24miss,1,2))+100,'FM0X')||
9 to_char(to_number(substr(p_yyyymmddhh24miss,3,2))+100,'FM0X')||
10 to_char(to_number(substr(p_yyyymmddhh24miss,5,2)),'FM0X')||
11 to_char(to_number(substr(p_yyyymmddhh24miss,7,2)),'FM0X')||
12 to_char(to_number(substr(p_yyyymmddhh24miss,9,2))+1,'FM0X')||
13 to_char(to_number(substr(p_yyyymmddhh24miss,11,2))+1,'FM0X')||
14 to_char(to_number(substr(p_yyyymmddhh24miss,13,2))+1,'FM0X')
15 )
16 );
17 end;
18 /
Procedure created.
SQL>
SQL> exec store_date('20160528211212')
PL/SQL procedure successfully completed.
SQL> select * from t;
X
--------------
7874051C160D0D
As you can see, the dates are stored in a compact 7-byte format. I’ve added 100 to the century and the year so we can also store negative dates (before 0AD) without any dramas. I’m quite impressed with my ingenuity here. I’m not going to have any of those “number of seconds since 1970” issues, where a 32-bit number might overflow etc etc.
So let us compare that to the DATE datatype.
SQL> create table t1 ( x date );
Table created.
SQL> insert into t1 values ( to_date('20160528211212','yyyymmddhh24miss'));
1 row created.
SQL> select dump(x) from t1;
DUMP(X)
--------------------------------------------------------------------------------
Typ=12 Len=7: 120,116,5,28,22,13,13
Let me convert that to plain old bytes so we can compare
SQL> create or replace
2 function dump_to_hex(p_str varchar2) return varchar2 is
3 l_str varchar2(100) := p_str;
4 l_elem varchar2(10);
5 l_hex varchar2(100);
6 begin
7 l_str := substr(l_str,instr(l_str,':')+2);
8 loop
9 l_elem := substr(l_str,1,instr(l_str,',')-1);
10 exit when l_elem is null;
11 l_hex := l_hex || to_char(to_number(l_elem),'FM0X');
12 l_str := substr(l_str,instr(l_str,',')+1);
13 end loop;
14 return l_hex;
15 end;
16 /
Function created.
SQL>
SQL> select dump_to_hex(dump(x)) from t1;
DUMP_TO_HEX(DUMP(X))
-----------------------------------------------------------------
7874051C160D
Oh…. Looks like someone beat me to it 
So if you’re thinking about re-inventing your own datatype for dates, perhaps just stick with the one that’s provided for you … it works just fine
Learning is not a spectator sport 
Never, ever, underestimate the power of a modern duhveloper to turn “really easy” into a nightmare for those charged with maintaining and optimizing the data storage and retrieval of an application.
Ah yes, it’s called “kewl coding”. Or something…
I hope that you didn’t post this on asktom.
I hope that nobody is naive enough to actually use this “raw” design , simply because it comes from an Expert.
Wanna bet?
🙂
Indeed….deliberately tried to keep this post firmly tongue in cheek 🙂