Some More Date functions.

What day is today in SQL?
select to_char(sysdate,'DAY') from dual;

Extending the above to other useful stuff:

First Day of the Month:
select to_char(trunc(sysdate,'MM'),'DAY' ) from dual;
Ex: WEDNESDAY

Last Day of the Month:
select to_char(last_day(sysdate),'DAY') from dual;
Ex: MONDAY

Last Date of the month :
select last_day(sysdate) from dual
Ex:31-Aug-2011

Add time to a given date:
select to_char(trunc(last_day(sysdate))+13/12,'DD-MON-RRRR HH24:MI:SS') from dual