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 then converts the truncated date value to a character format and returns the result.
The TRUNC function accepts two arguments, the first being the date that is to be truncated and the second being the format model. The format model is a series of characters that specifies how you want to truncate the given date. Table 7-1 lists the format models along with a description of each.
Table 7-1. Format Models for TRUNC
| Format Model | Description |
| MI | Returns the nearest minute |
| HH, HH12, HH24 | Returns the nearest hour |
| D, DY, DAY | Returns the first day of the week |
| W | Returns the same day of the week as the first day of the month |
| IW | Returns the same day of the week as the first day of ISO year |
| WW | Returns the same day of the week as the first day of the year |
| RM, MM, MON, MONTH | Rounds to the nearest first day of the month |
| Q | Rounds to the nearest quarter |
| I, IY, IYYY | Returns the ISO year |
| Y, YY, YYY, SYEAR, YEAR, YYYY | Rounds to the nearest first day of the year |
| CC, SCC | Returns one greater than the first two digits of a given four-digit year |
The solution to this recipe returns the first day of the given month using the format model MM.