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:

• Using Trunc() Function
• Using Extract() Function
• Using To_Char() Function

1. Using TRUNC() Function

The TRUNC() function in PL/SQL truncates a date to the specified unit, allowing us to extract the first day of the month.

Syntax:

TRUNC(date_expression, '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:

PL/SQL using TRUNC()

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.

2. Using EXTRACT() Function

The EXTRACT() function is utilized to extract the month and year components from the current system date, which are then used to construct the first day of the month.

Syntax:

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

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:

PL/SQL using EXTRACT()

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.

3. Using To_Char()

In this approach, the TO_CHAR() function is used to format the current system date and extract the month and year components.

Syntax:

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

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:

PL/SQL using TO_CHAR()

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 get the first day of the month in PL/SQL is very useful for many dates related operations. It is widely used for data analysis, reporting, and application logic. We can find it by using the TRUNC function and use it accordingly. Through this, we can easily retrieve the required information about the first day of the month.