How to Get the First Day of the Month in PL/SQL?

In PL/SQL programming, efficiently manipulating dates is crucial for various tasks, such as generating reports, calculating durations, or scheduling events. One common requirement is retrieving the first day of the month from a given date. In this article, we'll explore different approaches to achieve this task using PL/SQL, providing readers with comprehensive methods to handle date manipulation effectively.

How to Get the First Day of the Month in PL/SQL

When working with dates in PL/SQL, extracting the first day of the month from a given date can be essential for many applications. To address this need, we'll discuss three approaches:

Approach 1: Using TRUNC() Function

The TRUNC() function is the simplest and most commonly used approach for obtaining the first day of the month in PL/SQL. The TRUNC() function truncates a date to the specified unit (e.g., day, month, year), allowing you to extract only the desired portion of the date.

Syntax:

TRUNC(date_expression, 'MONTH')

• date_expression: The date from which the first day of the month is to be extracted.
• 'MONTH': The unit to truncate the date to (in this case, the month).

Query:

In this example, the current system date (SYSDATE) is truncated to the first day of the month using the TRUNC function. The resulting data is then displayed using the DBMS_OUTPUT.PUT_LINE statement.

DECLARE
    first_day DATE;
BEGIN
    first_day := TRUNC(SYSDATE, 'MONTH');
    DBMS_OUTPUT.PUT_LINE('First day of the month: ' || TO_CHAR(first_day, 'DD-MON-YYYY'));
END;

Output:

Explanation:

• The TRUNC() function is used to truncate the given date to the specified unit, in this case, 'MONTH', which makes sure that only the first day of the month remains.
• In the example, we use the SYSDATE function to get the current system date. We then apply TRUNC(SYSDATE, 'MONTH') to obtain the first day of the current month.
• The resulting date is assigned to the variable first_day, which is then displayed using the DBMS_OUTPUT.PUT_LINE() function.
• The TO_CHAR() function is used to format the date in the desired output format.

Approach 2: Using EXTRACT() Function

The EXTRACT() function allows you to extract specific components (e.g., year, month) from a date. You can use this to construct the first day of the month by extracting the year and month and combining them with a fixed day value.

Syntax:

TO_DATE(EXTRACT(YEAR FROM date_expression) || '-' || EXTRACT(MONTH FROM date_expression) || '-01', 'YYYY-MM-DD')

• EXTRACT(YEAR FROM date_expression): Retrieves the year component from the date.
• EXTRACT(MONTH FROM date_expression): Retrieves the month component from the date.
• TO_DATE(): Converts the concatenated string to a DATE data type.

Query:

DECLARE
    month_num NUMBER;
    year_num NUMBER;
    first_day DATE;
BEGIN
    SELECT EXTRACT(MONTH FROM SYSDATE) INTO month_num FROM DUAL;
    SELECT EXTRACT(YEAR FROM SYSDATE) INTO year_num FROM DUAL;
    first_day := TO_DATE('01-' || month_num || '-' || year_num, 'DD-MM-YYYY');
    DBMS_OUTPUT.PUT_LINE('First day of the month: ' || TO_CHAR(first_day, 'DD-MON-YYYY'));
END;

Output:

Explanation:

• Here, the EXTRACT() function is used to extract the month and year components from the current system date.
• We then construct a new date string with the first day of the month using these extracted values.
• This constructed date string is converted back to a DATE data type using the TO_DATE() function.
• Finally, the first_day variable is displayed using DBMS_OUTPUT.PUT_LINE() after formatting it using TO_CHAR() function.

Approach 3: Using To_Char()

The TO_CHAR() function can be used to format a date and extract specific components (such as month and year). By concatenating these components with a fixed day value, you can construct the first day of the month.

Syntax:

TO_DATE('01-' || TO_CHAR(SYSDATE, 'MM-YYYY'), 'DD-MM-YYYY')

• TO_CHAR(date_expression, 'MM-YYYY'): Formats the date to return the month and year.
• TO_DATE(): Converts the constructed string back to a DATE type

Query:

In this example, we will use To_Char() functions to format the Date retrieved from To_Date to To_Char for converting it from date type to Char type.

DECLARE
    first_day DATE;
BEGIN
    first_day := TO_DATE('01-' || TO_CHAR(SYSDATE, 'MM-YYYY'), 'DD-MM-YYYY');
    DBMS_OUTPUT.PUT_LINE('First day of the month: ' || TO_CHAR(first_day, 'DD-MON-YYYY'));
END;

Output:

Explanation:

• In this approach, the TO_CHAR() function is utilized to format the current system date and extract the month and year components.
• We then concatenate '01-' with the month and year obtained to form a string representing the first day of the month.
• This string is then converted back to a DATE data type using the TO_DATE() function.
• Finally, the first_day variable is displayed using DBMS_OUTPUT.PUT_LINE() after formatting it using TO_CHAR() function.

Conclusion

Knowing how to retrieve the first day of the month in Oracle is essential for date-related operations like report generation, billing, and scheduling. This article covered three approaches to achieve this:

• Using the TRUNC() function: The simplest and most efficient way to get the first day of the month.
• Using the EXTRACT() function: Useful for building dates dynamically.
• Using the TO_CHAR() function: Allows for custom date formatting.