Some Date Function Examples In Oracle

Determining the First Date or Day in a Month In Oracle

You need to dynamically determine the first date in a given month at runtime. Oracle’s TRUNC function can be used on dates to perform start-of-period calculations, such as the first day of the month.

select trunc(sysdate,'mm')
from dual;

TRUNC(SYS
---------
01-MAY-09

Our recipe was written on May 21st. Any arbitrary date, datetime, or timestamp expression can be
used.

Calculating the Day of the Week in Oracle

Determining the day of the week, and also indicate whether it’s considered a work day or weekend.

select to_char(sysdate,'Day') Day_Of_Week,
case
when to_char(sysdate,'D') in (1,7) then 'Weekend'
else 'Weekday'
end Day_Type
from dual;

Running my example on a Monday returns the following results.
DAY_OF_WEEK DAY_TYPE
----------- --------
Monday Weekday

Conversely, running my example on a Saturday shows this output.
DAY_OF_WEEK DAY_TYPE
----------- --------
Saturday Weekend

Converting Strings to Datetime Values in Oracle

select to_date('January 17, 2009', 'Month DD, YYYY') formatted_date
from dual;

See also Determining the dates of Easter of any year in Oracle
Detecting Leap Year In Oracle