Most DB2 installations creates their DB2 tables containing a timestamp column specifying when the row was created or last updated. To retrieve the rows in such a table created or changed on a specific date you can issue the following SQL statement:

SELECT *
FROM myTable
WHERE DATE(creationTimestamp) = :creationDate

It cannot be do much easier. The problem is that even if there exists an index on creationTimestamp DB2 will not use it even not in version 8 or 9. Several ways of solving this problem has been invented and I will introduce yet another one. This solution only requires a little rewriting of the above SQL statement:

SELECT *
FROM myTable
WHERE TRUNC_TIMESTAMP(creationTimestamp) = TIMESTAMP(:creationDate)

Under the assumption that hostvariable :creationDate is a CHAR(10) or equivalent, the SQL statement will now use an index where creationTimestamp is the first column in the index. This works for version 8 and 9, but I never had the chance to try it on version 7.

I was not aware of the function TRUNC_TIMESTAMP, until I was extremely annoyed with the fact that DB2 would not use an index in combination with the DATE function. In anger I tried it and to my surprise it worked. TRUNC_TIMESTAMP and its cousin ROUND_TIMESTAMP are weird functions which can truncate and round timestamps in several ways. I have not found any reasonable use for any of them besides the above trick. Now that I know of their existence I still have not found any other usage for them. These two functions were already introduced in DB2 version 7, which just shows how difficult it is to be aware of all the changes in DB2.