Try this for SQL (according to the tag):
SELECT DATENAME(dw, GETDATE())
-- or
SELECT DATENAME(dw, 1)
Try this for pl/sql:-
SELECT TO_CHAR((sysdate - (TO_CHAR(sysdate,'d') - 1)),'Day')
FROM DUAL;
Please don't forget to mark this as your answer if it helps you out.
Thanks