SQL Functions for Date Datatype in Oracle

SQL Functions for Date Datatype help programmers in doing many date related arithmetic. It allows adding months to a specific data, finding last day for the month of given date and so on. Here are the SQL functions for Date Datatype in Oracle.

In the following section SYSYDATE function is used in all examples. It is a no argument function that just returns the current date and time. The output format of this function is DD-MON-YYYY hh:mm:ss.

Date Arithmetic

In SQL you can use the +(sum) and – (minus) operators to perform calculations on dates. By adding or subtracting a number from a date you can get a date as output by adding or subtracting that number of days. You can simply subtract two given dates to get number of days between them.

SELECT SYSDATE+7 FROM DUAL;

This query will output the date after a week.

SELECT EMP_NAME, SYSDATE-DOB  FROM EMPLOYEES ;

This query will output the name and age of each employee in the EMPLOYEES table.

SQL Functions for Date Datatype

The following are the SQL function for date datatype in Oracle.

ADD_MONTHS

The ADD_Months function takes two arguments. First is the date to which you want to add some months and second is the number of months. If the second argument is positive the output is the date ahead of the first argument. If the second argument is negative then output is a past date from the date given as first argument by number of months specified.

Syntax

ADD_MONTHS(date1,mons)

Example

SELECT ADD_MONTHS(SYSDATE,-4) FROM DUAL;

The output is same date four by 4 months back.

SELECT ADD_MONTHS(SYSDATE, 4) FROM DUAL;

The output is same date 4 months in future.

SELECT ADD_MONTHS(ORDERDATE,1) ‘Delivery Date’ from tblOrders;

In this example orderdate in each row is displayed after adding 4 months. So, this function works on each row selected in the SELECT statement.

LAST _DAY

The LAST_DAY function takes only one argument. It returns the date of the last day of the month for the date passed as only argument.

Syntax

LAST_DAY (date1)

Example

SELECT SYSDATE ,LAST_DAY (SYSDATE) FROM DUAL;

The output is displaying the current date and the last day of the month of the current date

SELECT SYSDATE ,LAST_DAY (SYSDATE)+1  FROM DUAL;

The output is current date and next day after the last day of the month of the current date. This is done by adding 1 to the date returned by the LAST_DAY function.

SELECT ORDERDATE, LAST_DAY(ORDERDATE) “Last Date” from orderstbl;

The output displays the last date of each orderdate.

MONTHS_BETWEEN

The MONTHS_BETWEEN function takes two date arguments. It returns the number of months between the first date argument and the second date argument.   It works like this months(date1-date2). If date 1 is earlier and  date2 is later, output will be negative. If date 1 is later and  date1 is earlier, output will be positive. The output is in decimal form which can be rounded off by using the Numeric Round Function

Syntax

MONTHS_BETWEEN (date1, date2)

Example

SELECT MONTHS_BETWEEN (’01-JAN-2019’, SYSDATE) FROM DUAL;

Here the current date is 10 June 2019. The query returns the number of months from first day of year to today.  The output is a negative since the date1 is earlier than date2

SELECT MONTHS_BETWEEN ( ’01-JAN-20’, SYSDATE)  FROM DUAL;

Again the  current date is 10 June 2019. The query returns the number of months from today to the first day of next year.  The output is a positive since the date1 is later than date2

SELECT MONTHS_BETWEEN (ORDERDATE, SYSDATE) ‘ How many Months passed since ordered?’ from tblOrders;

Here the query is written to find that how many months are past since the customer has placed the order.

NEXT_DAY

The NEXT_DAY function takes one date argument and one string argument. It date of the day specified as second argument falling after date specified in first argument.  The second string argument can be specified as a day name or three character abbreviation of day name.

Syntax

NEXT_DAY (date1, WeekDay)

Example

SELECT NEXT_DAY (’01-JAN-2019′, ‘THU’) FROM DUAL;

SELECT SYSDATE, NEXT_DAY (SYSDATE, ‘SATURDAY’)  FROM DUAL;

SELECT ORDERDATE, NEXT_DAY (ORDERDATE, ‘WED’) “Next Wednesday falls on” from orderstbl;

ROUND

Just like ROUND function from numeric data, this function for Date data type is used to round a date on months and years. ROUND function takes the first argument as date and second argument as format to be used for rounding the first date argument.

Syntax

ROUND (date1, format)

Example

SELECT SYSDATE, ROUND (SYSDATE, ‘MM’) FROM DUAL;

SELECT SYSDATE+10, ROUND (SYSDATE+10, ‘MM’) FROM DUAL;

SELECT SYSDATE+10, ROUND (SYSDATE+10, ‘YY’) FROM DUAL;

SELECT ADD_MONTHS(SYSDATE,-3), ROUND (ADD_MONTHS(SYSDATE,-3), ‘YY’) FROM DUAL;

Note: SQL Functions for Date Datatype can be nested together or with String and Number functions to solve the complicated expressions.