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.

1. 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
   

2. 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
   

3. 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
   

4. 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
   

5. 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.

My Personal Notes arrow_drop_up

Save