Friday, January 16, 2009

Date missing when query with timestamp

The date datatype column in database is rather misleading. This is because it can store 2 types of format. One is without the timestamp (01/01/2009) and the other is with timestamp (01/01/2009 12:00:00 PM). Both format can store in the same column!

The problem is when programmer code their program on the first view of that date column. Let say it currently stored (01/01/2009) and the query will follows as "... where datecolumn = to_date('01/01/2009', 'DD/MM/YYYY' and ....".

Unfortunately, the other programmer thought that seems the column is a date field, he might just store the current date ('01/01/2009 12:34:56 PM') or sysdate which have timestamp. This will leaad to the first programmer's query unable to work because "01/01/2009" represent "01/01/2009 0:00:00 AM".

The solution for this problem is to format all date query to same format (e.g. "DD/MM/YYYY").
Example: "... where to_char(datecolumn, 'DD/MM/YYYY') = '01/01/2009' and ..."

(imagine if you have 1000 date query in a procedure, that will be pain in the butt)

Or just ask the database vendor to make up their mind, that the date datatype should and only should have one format.

Or database vendor create another date datatype. One with datetimestamp, one without.

Therefore, everybody live happily ever after.