Oracle and PL/SQL Recipes: A Problem-Solution Approach

7-7. Returning the First Day of a Given Month

Problem

You want to have the ability to obtain the name of the first day for a given month.

Solution

Write a PL/SQL function that accepts a date and applies the necessary functions to return the first day of month for the given date.

CREATE OR REPLACE FUNCTION first_day_of_month(in_date DATE) RETURN VARCHAR2 IS BEGIN   RETURN to_char(trunc(in_date,'MM'), 'DD-MON-YYYY'); END;

The function created in this solution will return the first day of the month that is passed into it because it is passed into the TRUNC function.

How It Works

The TRUNC function can be useful for returning information from a DATE type. In this case, it is used to return the first day of the month from the given date. The solution ...