Useful Date and Time Functions in PL/SQL

Date and Time Function formats are different various database. we are going to discuss most common functions used in Oracle database.

The function SYSDATE returns 7 bytes of data, which includes:

  • Century
  • Year
  • Month
  • Day
  • Hour
  • Minute
  • Second

1. Extract():
Oracle helps you to extract Year, Month and Day from a date using Extract() Function.

  • Example-1: Extracting Year:
    filter_none

    edit
    close

    play_arrow

    link
    brightness_4
    code

    SELECT SYSDATE AS CURRENT_DATE_TIME, EXTRACT(Year FROM SYSDATE) AS ONLY_CURRENT_YEAR
    FROM Dual

    chevron_right

    
    

    Output:

    CURRENT_DATE_TIME ONLY_CURRENT_YEAR
    05.Feb.2019 07:29:24 2019

    Explanation:
    Useful to retrieve only year from the System date/Current date or particular specified date.

  • Example-2: Extracting Month:
    filter_none

    edit
    close

    play_arrow

    link
    brightness_4
    code

    SELECT SYSDATE AS CURRENT_DATE_TIME, EXTRACT(Month FROM SYSDATE) AS ONLY_CURRENT_MONTH
    FROM Dual

    chevron_right

    
    

    Output:



    CURRENT_DATE_TIME ONLY_CURRENT_MONTH
    05.Feb.2019 07:29:24 Feb

    Explanation:
    Useful to retrieve only month from the System date/Current date or particular specified date.

  • Example-3: Extracting Day:
    filter_none

    edit
    close

    play_arrow

    link
    brightness_4
    code

    SELECT SYSDATE AS CURRENT_DATE_TIME, EXTRACT(Day FROM SYSDATE) AS ONLY_CURRENT_DAY
    FROM Dual

    chevron_right

    
    

    Output:

    CURRENT_DATE_TIME ONLY_CURRENT_DAY
    05.Feb.2019 07:29:24 5

    Explanation:
    Useful to retrieve only day from the System date/Current date or particular specified date.

2. ADD_MONTHS (date, n):
Using this method in PL/SQL you can add as well as subtract number of months(n) to a date. Here ‘n’ can be both negative or positive.

  • Example-4:
    filter_none

    edit
    close

    play_arrow

    link
    brightness_4
    code

    SELECT ADD_MONTHS(SYSDATE, -1)  AS PREV_MONTH, SYSDATE AS CURRENT_DATE
                                     ADD_MONTHS(SYSDATE, 1) as NEXT_MONTH
    FROM Dual

    chevron_right

    
    

    Output:

    PREV_MONTH CURRENT_DATE NEXT_MONTH
    02.Jan.2019 09:15:46 02.Feb.2019 09:15:46 02.Mar.2019 09:15:46

    Explanation:
    ADD_MONTHS function have two parameters one is date, where it could be any specified/particular date or System date as current date and second is ‘n’, it is an integer value could be positive or negative to get upcoming date or previous date.

3. LAST_DAY(date):
Using this method in PL/SQL you can get the last day in the month of specified date.

  • Example-5:
    filter_none

    edit
    close

    play_arrow

    link
    brightness_4
    code

    SELECT SYSDATE AS CURRENT_DATE, LAST_DAY(SYSDATE) AS LAST_DAY_OF_MONTH, 
                                    LAST_DAY(SYSDATE)+1 AS FIRST_DAY_OF_NEXT_MONTH
    FROM Dual

    chevron_right

    
    

    Output:

    CURRENT_DATE LAST_DAY_OF_MONTH FIRST_DAY_OF_NEXT_MONTH
    02.Feb.2019 09:32:00 28.Feb.2019 09:32:00 01.Mar.2019 09:32:00

    Explanation:
    In above example, we are getting current date using SYSDATE function and last date of the month would be retrieved using LAST_DAY function and this function be also helpful for retrieving the first day of the next month.

  • Example-6: Number of Days left in the month
    filter_none

    edit
    close

    play_arrow

    link
    brightness_4
    code

    SELECT SYSDATE AS CURRENT_DATE, LAST_DAY(SYSDATE) - SYSDATE AS DAYS_LEFT_IN_MONTH
    FROM Dual

    chevron_right

    
    

    Output:



    CURRENT_DATE DAYS_LEFT_IN_MONTH
    02.Feb.2019 09:32:00 26

4. MONTHS_BETWEEN (date1, date2):
Using this method in PL/SQL you can calculate the number of months between two entered dates date1 and date2. if date1 is later than date2 then the result would be positive and if date1 is earlier than date2 then result is negative.

Note:
If a fractional month is calculated, the MONTHS_BETWEEN function calculates the fraction based on a 31-day month.

  • Example-7:
    filter_none

    edit
    close

    play_arrow

    link
    brightness_4
    code

    SELECT MONTHS_BETWEEN (TO_DATE ('01-07-2003', 'dd-mm-yyyy'), 
                           TO_DATE ('14-03-2003', 'dd-mm-yyyy')) AS NUMBER_OF_MONTHS
    FROM Dual

    chevron_right

    
    

    Output:

    NUMBER_OF_MONTHS
    3.58

    Explanation:
    Here date1 and date2 are not on the same day of the month that’s why we are getting the value in fractions, as well as date1 is later than date2 so the resulting value is in integers.
    Eneterd date should be in particular date format, that is the reason of using TO_DATE function while comparison within MONTHS_BETWEEN function.

    Let’s select the number of months an employee has worked for the company.

  • Example-8:
    filter_none

    edit
    close

    play_arrow

    link
    brightness_4
    code

    SELECT MONTHS_BETWEEN (SYSDATE, DATE_OF_HIRE) AS NUMBER_OF_MONTHS
    FROM Employees

    chevron_right

    
    

    Input:

    SYSTEM_DATE DATE_OF_HIRE
    02-02-2019 31-10-2017
    02-02-2019 03-12-2017
    02-02-2019 24-09-2018
    02-02-2019 22-12-2016
    02-02-2019 18-06-2018

    Output:

    NUMBER_OF_MONTHS
    15.064
    13.967
    4.290
    25.354
    7.483

5. NEXT_DAY(date, day_of_week):
It will return the upcoming date of the first weekday that is later than the entered date.It has two parameters first date where, system date or specified date can be entered; second day of week which should be in character form.

  • Example-9:
    filter_none

    edit
    close

    play_arrow

    link
    brightness_4
    code

    SELECT NEXT_DAY(SYSDATE, 'SUNDAY') AS NEXT_SUNDAY
    FROM Dual

    chevron_right

    
    

    Output:

    NEXT_SUNDAY
    17-FEB-2019

    Explanation:
    It will help to provide the next upcoming date corresponding to the day, return type is always DATE regardless of datatype date. The second parameter must be a day of the week either full name or abbreviated.



My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. 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.




Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.