How to use EXTRACT() Function In SQL
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:
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.
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.