Jul 012013
 
alter session set nls_date_format = 
  'dd-mon-yyyy hh:mi am';

select sysdate "Clearly 9am on 1 July 2013" 
from   dual;

Clearly 9am on 1 July 2013
--------------------------
01-jul-2013 09:00 AM 

alter session set nls_date_format = 
  'dd-mm-yy hh:mi';

select sysdate "9 am or pm? 1 July or 7 Jan?" 
from   dual;

9 am or pm? 1 July or 7 Jan?
----------------------------
01-07-13 09:00           

alter session set nls_date_format = 
  '"''union select * from sql_injection"';

select sysdate "Make sure you're using binds!" 
from   dual;

Make sure you're using binds!
----------------------------------
'union select * from sql_injection
    
You can put pretty much anything between the quotes in the last example, potentially 
leading to all sorts of weird and wonderful behaviour for people reading the data and 
relying on implicit conversions!

For an explanation of the SQL injection risk, read Tom Kyte’s write up.

Jan 162013
 
select last_day_of_the_month - 
       interval '1' month "throws ORA-01839"
from   months
where  total_days > prev_month_days;
Annoyingly, adding/subtracting intervals of a month just changes the month part of the date, 
without adjusting the days accordingly. You'll need to use add_months() if you want the days to be resolved

For a script to see this in action, use this.