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
from dual;
FORMATTED_DATE
————–
17-JAN-09
See also Determining the dates of Easter of any year in Oracle
Detecting Leap Year In Oracle
