Let’s see, What is Date Functions in SQL. In SQL, Date Functions are coming in Single Row Functions. Date functions help us to perform the operation on Dates.
Date Functions are functions that take values that are of datatype DATE as input and return values of data types DATE, except for the MONTHS_BETWEEN function, which returns a number as output.
Date Functions in SQL
The Oracle Database stores the dates in the internal numeric format: Century, year, month, day, hour, minutes and seconds.
The default date display format is “DD-MON-YY”.
Arithmetic with Dates
We can perform the arithmetic operations on the dates. SYSDATE function gives us the system date.
We can perform addition and subtraction on Date values.
SQL> Select (SYSDATE- hire_Date) as Days from Employees;
Add hours to a date by dividing the number of hours by 24.
SQL> Select (SYSDATE- hire_Date)/7 as Weeks from Employees;
Date Function in SQL with Example
1. SYSDATE : The Sysdate function returns the current oracle database server date and time.
SQL> SELECT sysdate from Employees;
2. Arithmetic with Dates : You can add or subtract the number of days or hours to the dates. You can also subtract the dates.
SQL> SELECT sysdate+5 “add_days” FROM DUAL;
SQL> SELECT sysdate-2 “sub_days” FROM DUAL;
SQL> SELECT sysdate+5/24 “add_hours” FROM DUAL;
SQL> SELECT sysdate-2/24 “sub_hours” FROM DUAL;
3. MONTHS_BETWEEN : The Months_Between function returns the number of months between the two given dates.
SQL> SELECT months_between(sysdate,hire_date) FROM EMPLOYEES;
4. ADD_MONTHS : The Add_Months is used to add or subtract the number of calendar months to the given date.
SQL> SELECT add_months(sysdate,3) FROM DUAL;
5. NEXT_DAY : The Next_Day function finds the date of the next specified day of the week.
SQL> SELECT next_day(sysdate,’FRIDAY’) FROM DUAL;
6. LAST_DAY : The Last_Day function returns the last day of the month.
SQL> SELECT last_day(sysdate) FROM DUAL;
7. ROUND : The Round function returns the date rounded to the specified format.
Syntax – Round(date [,’fmt’])
Example – ROUND (SYSDATE, ‘MONTH’) — Result – 01-AUG-15
ROUND (SYSDATE, ‘Year’) — Result – 01-JAN-16
These are the Date functions. The date is very important data type in SQL. Please practice date queries on your local machine as well.