Date Functions in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

Date Functions in Oracle with Examples

In this article, I am going to discuss Date Functions in Oracle with Examples. Please read our previous article where we discussed String Functions in Oracle with Examples. At the end of this article, you will understand the following Oracle Date Functions with examples.

1. SYSDATE Function in Oracle
2. ADD_MONTHS() Function in Oracle
3. LAST_DAY() Function in Oracle
4. NEXT_DAY() Date Function in Oracle
5. MONTHS_BETWEEN() Function in Oracle
6. CURRENT_DATE() Function in Oracle

Oracle Date Functions:

The Oracle Date functions can be defined as a set of functions that operate on date and time and allows the developer to retrieve the current date and time in a particular time zone or extract only the date/month/year or more complex actions like extracting the last day of the month/next day/session time zone, etc. It also consists of functions that can be used to convert a Date value to a character string or convert a date that is in a character string to a Date value.

SYSDATE Function in Oracle:

The SYSDATE function in Oracle is used to return the current date and time set for the operating system on which the database resides. The data type of the returned value is DATE, and the format returned depends on the value of the NLS_DATE_FORMAT initialization parameter. The function requires no arguments. In distributed SQL statements, this function returns the date and time set for the operating system of your local database. You cannot use this function in the condition of a CHECK constraint.

Example:
SELECT SYSDATE FROM DUAL;
Output:

Example:
SELECT SYSDATE+10 FROM DUAL;
Output:

Example:
SELECT SYSDATE-10 FROM DUAL;
Output:

The SYSDATE() function in oracle is the most commonly used date function. The Oracle date function SYSDATE() returns the current date and time in the default Oracle date format. The default format for the date returned is MM-DD-YY. It is also very common to use the Oracle date function SYSDATE() in conjunction with the to_char() function. For example,

SELECT TO_CHAR(SYSDATE, ‘MM-DD-YYYY HH:MI:SS’) “NOW” FROM DUAL;

Output:

This Oracle date function returns a string containing not only the current date but also the current time down to the second.

ADD_MONTHS() Function in Oracle:

The ADD_MONTHS date function in Oracle is used to return the date with a given number of months added (date plus integer months). The following is the syntax to use the ADD_MONTHS date function in Oracle.

Syntax: ADD_MONTHS(date, integer)

Parameters:

1. Date: It can be a DateTime value or any value that can be implicitly converted to DATE.
2. Integer: It can be an integer number or any value that can be implicitly converted to an integer.

Note: The return type is always going to be DATE, regardless of the datatype of date. If the date is the last day of the month or if the resulting month has fewer days than the day component of the date, then the result is the last day of the resulting month. Otherwise, the result has the same day component as the date.

Example: To get the today system day date after 2 months.
SELECT ADD_MONTHS(SYSDATE, 2) FROM DUAL;
Output:

Example: To get the today system day date before 2 months.
SELECT ADD_MONTHS(SYSDATE,-2) FROM DUAL;
Output:

LAST_DAY() Function in Oracle:

The LAST_DAY() date function in Oracle is used to return the last day of the month that contains a date. The return type is always going to be DATE type regardless of the data type of date. The syntax to use the LAST_DAY() date function in Oracle is given below.

Syntax: LAST_DAY(date)

Parameter:

1. date: The specified date value.

Example:
SELECT LAST_DAY(SYSDATE) FROM DUAL;
Output:

Example: The following query determines how many days are left in the current month.

SELECT SYSDATE, LAST_DAY(SYSDATE) "Last", LAST_DAY(SYSDATE) - SYSDATE "Days Left" FROM DUAL;

Output:

NEXT_DAY() Date Function in Oracle:

The NEXT_DAY date function in Oracle is used to return the date of the first weekday that is later than the date. The syntax to use the oracle NEXT_DAY function is given below.

Syntax: NEXT_DAY(DATE,'<DAY_NAME>’)

Parameters:

1. Date: A date value used to get the next weekday.
2. DAY_NAME: must be a day of the week in the date language of your session, either the full name or the abbreviation. The minimum number of letters required is the number of letters in the abbreviated version. Any characters immediately following the valid abbreviation are ignored.

Note: The return type is always going to be DATE regardless of the data type of date. The return value has the same hours, minutes, and seconds component as the argument date.

Example:

SELECT NEXT_DAY(SYSDATE,'MONDAY') FROM DUAL;

Output:

Example:
SELECT NEXT_DAY(SYSDATE,’FRIDAY’) FROM DUAL;
Output:

MONTHS_BETWEEN() Function in Oracle:

The MONTHS_BETWEEN() date function in oracle is used to get the number of months between two dates (date1, date2). While working with the MONTHS_BETWEEN() date function in oracle, we need to keep in mind the following conditions:

1. If date1 is later than date2, then the result is positive.
2. If date1 is earlier than date2, then the result is negative.
3. If date1 and date2 are either the same days of the month or both last days of months, then the result is always an integer.
4. Otherwise, Oracle Database calculates the fractional portion of the result based on a 31-day month and considers the difference in time components date1 and date2.

Following is the syntax to use the MONTHS_BETWEEN() date function in oracle.

Syntax: MONTHS_BETWEEN(DATE1, DATE2)

Parameters:

1. DATE1: The first date
2. DATE2: The second date.

Example:
SELECT MONTHS_BETWEEN(’05-JAN-81′,’05-JAN-80′) FROM DUAL;
Output:

Example:
SELECT MONTHS_BETWEEN(’05-JAN-80′,’05-JAN-81′) FROM DUAL;
Output:

Note: Here, DATE1 is always greater than DATE2 otherwise oracle returns a negative value.

CURRENT_DATE() Function in Oracle:

The CURRENT_DATE date function in Oracle is used to return the current date in the session time zone, in a value in the Gregorian calendar of datatype DATE. This function does not take any parameter. Following is the syntax to use the CURRENT_DATE function in Oracle.

Syntax: CURRENT_DATE

Example: The following statement shows the current date in ‘DD-MON-YYYY HH24:MI:SS’ format
ALTER SESSION SET NLS_DATE_FORMAT = ‘DD-MON-YYYY HH24:MI:SS’;
Output:

Let display the current date and session time zone.:
SELECT CURRENT_DATE, SESSIONTIMEZONE FROM DUAL;
Output:

Now let us change the session timezone using ALTER SESSION command:
ALTER SESSION SET TIME_ZONE = ‘-2:0’;
Output:

Now check the current date and session time zone:
SELECT CURRENT_DATE, SESSIONTIMEZONE FROM DUAL;
Output:

In the next article, I am going to discuss Conversion Functions in Oracle with Examples. Here, in this article, I try to explain Date Functions in Oracle with Examples and I hope you enjoy this article.