PL/SQL Date functions

Oracle PL/SQL provides several built-in date functions that can be used to manipulate and extract information from date and timestamp data types.

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.

DAY function returns the day of the month (1-31) of a given date.

EXTRACT function can be used to extract a specific field from a date or timestamp, such as the month, year, or hour.

DAYNAME function returns the name of the day of the week for a given date, while the MONTH function returns the month of the year (1-12) for a given date.

YEAR function returns the year of a given date.

WEEKDAY function returns the day of the week (1-7) for a given date, with 1 being Sunday and 7 being Saturday.

DATE function can be used to convert a character string or separate date components (year, month, day) into a date value.

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 PL/SQL. It’s worth noting that PL/SQL date functions are based on the Oracle Database date data type, which stores both date and time information.