Date and Time Functions in PL/SQL

Date and Time Functions in PL/SQL are useful for date and time operations like extracting date components, performing date arithmetic, and converting between date formats.

There are many useful Date and Time functions in PL/SQL. A table of such functions along with their description is given below:

Let’s understand each of the PL/SQL Date and Time functions with examples.

The SQL SYSDATE function returns the current date and time set for operating system on which the database resides.

The function SYSDATE returns 7 bytes of data, which includes:

• Century
• Year
• Month
• Day
• Hour
• Minute
• Second

Oracle helps you to extract Year, Month, and Day from a date using the Extract() Function.

EXTRACT() Function Example for Extracting year

Query:

SELECT SYSDATE AS CURRENT_DATE_TIME, 
EXTRACT(Year FROM SYSDATE) AS ONLY_CURRENT_YEAR
FROM Dual

Output:

Explanation: Useful to retrieve only the year from the System date/Current date or particular specified date.

EXTRACT() Function Example for Extracting Month

Query:

SELECT SYSDATE AS CURRENT_DATE_TIME, 
EXTRACT(Month FROM SYSDATE) AS ONLY_CURRENT_MONTH
FROM Dual

Output:

Explanation: Useful to retrieve only a month from the System date/Current date or particular specified date.

Using this method in PL/SQL you can add as well as subtract a number of months(n) to a date. Here ‘n’ can be both negative or positive.

ADD_MONTHS (date, n) Example

Query:

SELECT ADD_MONTHS(SYSDATE, -1) AS PREV_MONTH, SYSDATE AS CURRENT_DATE,                                 
ADD_MONTHS(SYSDATE, 1) AS NEXT_MONTH
FROM Dual

Output:

Explanation: ADD_MONTHS function has two parameters one is a date, where it could be any specified/particular date or System date as current date and second is ‘n’, it is an integer value could be positive or negative to get upcoming date or previous date.

Using this method in PL/SQL you can get the last day in the month of specified date.

LAST_DAY() Function Example

Query:

SELECT SYSDATE AS CURRENT_DATE, LAST_DAY(SYSDATE) AS LAST_DAY_OF_MONTH,                                 
LAST_DAY(SYSDATE)+1 AS FIRST_DAY_OF_NEXT_MONTH
FROM Dual

Output:

Explanation: In above example, we are getting current date using SYSDATE function and last date of the month would be retrieved using LAST_DAY function and this function be also helpful for retrieving the first day of the next month.

Using this method in PL/SQL you can calculate the number of months between two entered dates date1 and date2. if date1 is later than date2 then the result would be positive and if date1 is earlier than date2 then result is negative.

Note: If a fractional month is calculated, the MONTHS_BETWEEN function calculates the fraction based on a 31-day month.

MONTHS_BETWEEN () Function Example

Query :

SELECT MONTHS_BETWEEN (TO_DATE ('01-07-2003', 'dd-mm-yyyy'),
                     TO_DATE ('14-03-2003', 'dd-mm-yyyy')) AS NUMBER_OF_MONTHS
FROM Dual

Output:

Explanation: Here date1 and date2 are not on the same day of the month that’s why we are getting the value in fractions, as well as date1 is later than date2 so the resulting value is in integers. Entered date should be in particular date format, that is the reason of using TO_DATE function while comparison within MONTHS_BETWEEN function. Let’s select the number of months an employee has worked for the company.

It will return the upcoming date of the first weekday that is later than the entered date.It has two parameters first date where, system date or specified date can be entered; second day of week which should be in character form.

NEXT_DAY() Function Example 

Query:

SELECT NEXT_DAY(SYSDATE, 'SUNDAY') AS NEXT_SUNDAY
FROM Dual

Output:

Explanation: It will help to provide the next upcoming date corresponding to the day, return type is always DATE regardless of datatype date. The second parameter must be a day of the week either full name or abbreviated.