how about these for reasons:
BECAUSE IT WOULD BE STUPID TO USE THE WRONG DATATYPE TO STORE SOMETHING.
BECAUSE IT WOULD MAKE THE DEVELOPERS LOOK STUPID.
BECAUSE IT IS WRONG.
BECAUSE IT FLIES IN THE FACE OF COMMON SENSE.
BECAUSE IT WOULD ENSURE YOU WOULD HAVE THE 29th of Feb, 2009 in your table (among other really bad dates)
BECAUSE YOU WOULD SPEND LOTS OF CPU CYCLES CONVERTING THIS STRING INTO A DATE again in order to do things like..... math - how many days between, give me everything within one month of this.... etc
I'd love to hear from them why they think this would be "smart", "intelligent", a "good idea", "superior to using the right datatype"
ops$tkyte%ORA10GR2> create table t ( str_date, date_date, number_date, data )
2 as
3 select to_char( dt+rownum,'yyyymmdd' ),
4 dt+rownum,
5 to_number( to_char( dt+rownum,'yyyymmdd' ) ),
6 rpad('*',45,'*')
7 from (select to_date('01-jan-1995','dd-mon-yyyy') dt
8 from all_objects)
9 /
Table created.
ops$tkyte%ORA10GR2> create index t_str_date_idx on t(str_date);
Index created.
ops$tkyte%ORA10GR2> create index t_date_date_idx on t(date_date);
Index created.
ops$tkyte%ORA10GR2> create index t_number_date_idx on t(number_date);
Index created.
ops$tkyte%ORA10GR2> begin
2 dbms_stats.gather_table_stats
3 ( user, 'T',
4 method_opt=> 'for all indexed columns',
5 cascade=> true );
6 end;
7 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> set autotrace on explain
ops$tkyte%ORA10GR2> select *
2 from t
3 where str_date between '20001231' and '20010101';
STR_DATE DATE_DATE NUMBER_DATE DATA
-------- --------- ----------- ---------------------------------------------
20001231 31-DEC-00 20001231 *********************************************
20010101 01-JAN-01 20010101 *********************************************
Execution Plan
----------------------------------------------------------
Plan hash value: 623125948
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 298 | 14006 | 6 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 298 | 14006 | 6 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_STR_DATE_IDX | 298 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("STR_DATE">='20001231' AND "STR_DATE"<='20010101')
ops$tkyte%ORA10GR2> select *
2 from t
3 where number_date between 20001231 and 20010101;
STR_DATE DATE_DATE NUMBER_DATE DATA
-------- --------- ----------- ---------------------------------------------
20001231 31-DEC-00 20001231 *********************************************
20010101 01-JAN-01 20010101 *********************************************
Execution Plan
----------------------------------------------------------
Plan hash value: 2837589515
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 297 | 13959 | 6 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 297 | 13959 | 6 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_NUMBER_DATE_IDX | 297 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("NUMBER_DATE">=20001231 AND "NUMBER_DATE"<=20010101)
ops$tkyte%ORA10GR2> select * from t where date_date
2 between to_date('20001231','yyyymmdd') and to_date('20010101','yyyymmdd');
STR_DATE DATE_DATE NUMBER_DATE DATA
-------- --------- ----------- ---------------------------------------------
20001231 31-DEC-00 20001231 *********************************************
20010101 01-JAN-01 20010101 *********************************************
Execution Plan
----------------------------------------------------------
Plan hash value: 546586007
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 47 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 47 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_DATE_DATE_IDX | 1 | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DATE_DATE">=TO_DATE('2000-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
"DATE_DATE"<=TO_DATE('2001-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
ops$tkyte%ORA10GR2> set autotrace off
ops$tkyte%ORA10GR2>