Date manipulating functions in SQL
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.
Syntax :
ADD_MONTHS(d, n)
Example –
Select ADD_MONTHS(SYSDATE, 4)
"Add months" from dual;
Output :
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.
Syntax :
LAST_DAY(date)
Example-
Select SYSDATE, LAST_DAY(SYSDATE)
"Last Day" from dual;
Output :
SYSDATE Last Day
01-NOV-04 30-NOV-04
- MONTHS_BETWEEN :
Returns number of months between date1 and date2.
Syntax :
MONTHS_BETWEEN(date1, date2)
Example –
Select MONTHS_BETWEEN('02-FEB-00', '01-JAN-00')
"Months" from dual;
Output :
Months
1
- 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.
Syntax :
NEXT_DAY(date, char)
Example –
SELECT NEXT_DAY('06-JUL-02', 'saturday')
"Next day" from dual;
here it returns the date of next saturday.
Output :
Next day
13-JUL-02
- NEW_TIME :
Returns the date after converting it from time zone 1 to a date in time zone 2.
Syntax :
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 |
Example –
Select NEW_TIME(To_date('2004/07/01 01:45', 'yyyy/mm/dd HH24:MI'),
'AST', 'MST') "MST" from dual;
Output :
MST
30-JUN-04
This converts an Atlantic standard time into mountain standard time.
Last Updated :
21 Oct, 2020
Like Article
Save Article
Share your thoughts in the comments
Please Login to comment...