Oracle PL/SQL provides several built-in date functions that can be used to manipulate and extract information from date and timestamp data types. These functions make it easy to perform common tasks such as adding or subtracting days from a date, extracting specific components of a date (such as the month or year), and formatting dates in various ways. In this answer, we will explore some of the most commonly used PL/SQL date functions.
CURRENT_DATE function returns the current date in the session’s time zone.
CURRENT_TIMESTAMP function returns the current timestamp in the session’s time zone.
LOCALTIMESTAMP function returns the current timestamp in the local time zone, while the SYSTIMESTAMP function returns the current timestamp in the system time zone.
EXTRACT function can be used to extract a specific field from a date or timestamp, such as the month, year, or hour.
SYSDATE function returns the current date and time on the database server. Example:
SELECT SYSDATE FROM DUAL;
ADD_MONTHS function adds a specified number of months to a date. Example:
SELECT ADD_MONTHS(SYSDATE, 6) FROM DUAL;
MONTHS_BETWEEN function calculates the number of months between two dates. Example:
SELECT MONTHS_BETWEEN(TO_DATE('01-10-2022','DD-MM-YYYY'), TO_DATE('01-01-2022','DD-MM-YYYY'))
FROM DUAL;
NEXT_DAY function returns the date of the next specified day of the week after a given date. Example:
SELECT NEXT_DAY(SYSDATE, 'MONDAY') FROM DUAL;
LAST_DAY function returns the last day of the month of a given date. Example:
SELECT LAST_DAY(SYSDATE) FROM DUAL;
ROUND function rounds a date to the nearest specified unit of time (e.g. day, month, year). Example:
SELECT ROUND(SYSDATE, 'MONTH') FROM DUAL;
TRUNC function truncates a date to the specified unit of time. Example:
SELECT TRUNC(SYSDATE, 'MONTH') FROM DUAL;
These are just a few examples of the many date functions available in Oracle PL/SQL. By leveraging these functions, developers can easily manipulate and format date and time data to meet the needs of their applications.