Pitfall when working with DATE type

January 29th, 2007 by Rajender Singh · No Comments

Today I came across very simple but important concept which may look simple but can give a headache if we don’t understand or give importance to it.

When storing DATE type the database stores the time component also.

So without truncating or converting into string if we compare the transaction based on column of date type then it will not show it equal to your comparing data parameter as it will be comparing the time component also.

See the testing below:

CHOOBEEMAIN> SET TIME ON
20:43:51 CHOOBEEMAIN> CREATE TABLE test1 (adate DATE);

Table created.

20:44:10 CHOOBEEMAIN> CREATE TABLE test2(bdate DATE);

Table created.

20:44:50 CHOOBEEMAIN> INSERT INTO test1 VALUES(sysdate);

1 row created.

20:45:07 CHOOBEEMAIN> INSERT INTO test2 VALUES(sysdate);

1 row created.

20:45:20 CHOOBEEMAIN> commit;

Commit complete.

20:45:23 CHOOBEEMAIN> SELECT * FROM test1, test2 WHERE test1.adate = test2.bdate;