Open In App
Related Articles

Date manipulating functions in SQL

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Report issue
Report
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)
    ValueDescriptionValueDescription
    ASTAtlantic Standard TimeADTAtlantic Daylight Time
    BSTBering Standard TimeBDTBering daylight Time
    CSTCentral Standard TimeCDTCentral daylightTime
    GMTGreenwich Mean TimeNSTNewfoundland standard time
    HDTAlaska -Hawaii daylight TimeHSTAlaska -Hawaii standard Time
    MDTMountain daylight TimeMSTMountain standard Time
    PSTpacific standard TimePDTpacific 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
Previous
Next
Share your thoughts in the comments
Similar Reads