I always forget how to do this.
UPDATE foo SET bar_date = TO_DATE( '2005-09-29', 'YYYY-MM-DD') WHERE foo_id=51
SELECT TO_CHAR(bar_date, 'yyyy-mm-dd hh24:mi:ss') FROM foo;
SELECT * FROM foo WHERE bar_date > TO_DATE('08/16/2006', 'MM/DD/YYYY')
When you need a date in a pinch for inserting test data use sysdate.
INSERT INTO foo (bar_date) VALUE (sysdate)
A good article about this is Oracle, SQL, Dates and Timestamps.