To manipulate and fetch date values from the table, oracle has inbuilt date functions. Here, we are going to cover
Date manipulating functions in SQL.
- ADD_MONTHS :
It will add the total number of months specified in n to the date parameter and Returns date.
Select ADD_MONTHS(SYSDATE, 4) "Add months" from dual;
Add Months 01-NOV-04
- LAST_DAY :
In the LAST_DAY function, It will return the last date of the month, and for the specified month like if the system date is 1 Nov then it will return 30 Nov.
Select SYSDATE, LAST_DAY(SYSDATE) "Last Day" from dual;
SYSDATE Last Day 01-NOV-04 30-NOV-04
- MONTHS_BETWEEN :
Returns number of months between date1 and date2.
Select MONTHS_BETWEEN('02-FEB-00', '01-JAN-00') "Months" from dual;
- NEXT_DAY :
It will return the date of the first weekday that comes after the date specified in date parameter. char specified should be someday of the week.
SELECT NEXT_DAY('06-JUL-02', 'saturday') "Next day" from dual;
here it returns the date of next saturday.
Next day 13-JUL-02
- NEW_TIME :
Returns the date after converting it from time zone 1 to a date in time zone 2.
NEW_TIME(date, zone1, zone2)
Value Description Value Description AST Atlantic Standard Time ADT Atlantic Daylight Time BST Bering Standard Time BDT Bering daylight Time CST Central Standard Time CDT Central daylightTime GMT Greenwich Mean Time NST Newfoundland standard time HDT Alaska -Hawaii daylight Time HST Alaska -Hawaii standard Time MDT Mountain daylight Time MST Mountain standard Time PST pacific standard Time PDT pacific daylight Time
Select NEW_TIME(To_date('2004/07/01 01:45', 'yyyy/mm/dd HH24:MI'), 'AST', 'MST') "MST" from dual;
This converts an Atlantic standard time into mountain standard time.
- SQL | Date functions
- SQL | Date Functions (Set-1)
- SQL | Date Functions (Set-2)
- Useful Date and Time Functions in PL/SQL
- SQL | Functions (Aggregate and Scalar Functions)
- SQL | Difference between functions and stored procedures in PL/SQL
- Difference between Structured Query Language (SQL) and Transact-SQL (T-SQL)
- Numeric and Date-time data types in SQL Server
- SQL | NULL functions
- SQL | Character Functions with Examples
- SQL general functions | NVL, NVL2, DECODE, COALESCE, NULLIF, LNNVL and NANVL
- SQL Server Mathematical functions (SQRT, PI, SQUARE, ROUND, CEILING & FLOOR)
- SQL | Numeric Functions
- Functions in PL/SQL
- SQL | String functions
- SQL | Advanced Functions
- Categories of SQL Functions
- Window functions in SQL
- Deterministic and Nondeterministic Functions in SQL Server
- SQL | Procedures in PL/SQL
If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to email@example.com. See your article appearing on the GeeksforGeeks main page and help other Geeks.
Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.