Skip to content
Related Articles

Related Articles

Improve Article
Date manipulating functions in SQL
  • Last Updated : 21 Oct, 2020

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.

My Personal Notes arrow_drop_up
Recommended Articles
Page :