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.
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.
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.
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.
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.
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.
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
MONTHS_BETWEEN (date1, date2)
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;
|ORDERDATE||How many Months passed since ordered?|
Here the query is written to find that how many months are past since the customer has placed the order.
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.
NEXT_DAY (date1, WeekDay)
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;
|Next Wednesday falls on|
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.
ROUND (date1, format)
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.