How to use To_Char() In SQL

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:

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.

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.