SYSDATE or TRUNC(SYSDATE)?

One of the common mistakes non-HRMS developers make when they start with HRMS is to use sysdate instead of trunc(sysdate).

While the procurement, financials and the other modules use the time component heavily, HRMS does not capture the time component.

Take an employee record with the following data..

Effective Start Date: 01-Jan-2009 and Effective End Date: 07-Aug-2009.

On the 7th of August, If we run a query like this, we will expect this employee to be present.

select *
from per_all_people_f
where sysdate between effective_start_date and effective_end_date

But this employee wont be in the results.

Because, as sysdate(lets say '07-Aug-2009 12:34:56') will be compared against '07-Aug-2009 00:00:00' and the employee will be left.

Cheers,
Ganesh