How to Add an Interval to A Date In Oracle?

9 minutes read

In Oracle, you can add an interval to a date by using the INTERVAL keyword followed by the desired time unit (such as 'DAY', 'MONTH', 'YEAR', etc.) and the number of units you want to add.


For example, if you want to add 5 days to a given date, you can use the following SQL query:

1
2
SELECT date_column + INTERVAL '5' DAY
FROM your_table_name;


Similarly, if you want to add 2 months to a date, you can use:

1
2
SELECT date_column + INTERVAL '2' MONTH
FROM your_table_name;


This way, you can easily add intervals to dates in Oracle using SQL queries.

Best Oracle Database Books of June 2024

1
OCA Oracle Database SQL Exam Guide (Exam 1Z0-071) (Oracle Press)

Rating is 5 out of 5

OCA Oracle Database SQL Exam Guide (Exam 1Z0-071) (Oracle Press)

2
Oracle PL/SQL Programming: Covers Versions Through Oracle Database 12c

Rating is 4.9 out of 5

Oracle PL/SQL Programming: Covers Versions Through Oracle Database 12c

  • O Reilly Media
3
Oracle Database 12c PL/SQL Programming

Rating is 4.8 out of 5

Oracle Database 12c PL/SQL Programming

4
Beginning Oracle Database 12c Administration: From Novice to Professional

Rating is 4.7 out of 5

Beginning Oracle Database 12c Administration: From Novice to Professional

5
Expert Oracle Database Architecture: Techniques and Solutions for High Performance and Productivity

Rating is 4.6 out of 5

Expert Oracle Database Architecture: Techniques and Solutions for High Performance and Productivity

6
Expert Oracle Database Architecture

Rating is 4.5 out of 5

Expert Oracle Database Architecture

  • Apress
7
Oracle Database Application Security: With Oracle Internet Directory, Oracle Access Manager, and Oracle Identity Manager

Rating is 4.4 out of 5

Oracle Database Application Security: With Oracle Internet Directory, Oracle Access Manager, and Oracle Identity Manager

8
Oracle Database 12c PL/SQL Advanced Programming Techniques

Rating is 4.3 out of 5

Oracle Database 12c PL/SQL Advanced Programming Techniques

9
Oracle Database 11g SQL (Oracle Press)

Rating is 4.2 out of 5

Oracle Database 11g SQL (Oracle Press)

10
Oracle 12c For Dummies

Rating is 4.1 out of 5

Oracle 12c For Dummies


How to add business days to a date in Oracle?

One way to add business days to a date in Oracle is to utilize the "NEXT_DAY" function in conjunction with a loop that calculates the desired number of business days to add.


Here is an example code snippet that shows how to add 5 business days to a given date:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
DECLARE
    v_date DATE := TO_DATE('01-01-2022', 'dd-mm-yyyy'); -- Date to which business days will be added
    v_business_days_number NUMBER := 5; -- Number of business days to add
    
    BEGIN
        FOR i IN 1..v_business_days_number
        LOOP
            v_date := NEXT_DAY(v_date+1, 'MONDAY'); -- Adding one day to the current date and finding the next Monday
        END LOOP;
        
        DBMS_OUTPUT.PUT_LINE('Date after adding ' || v_business_days_number || ' business days: ' || TO_CHAR(v_date, 'dd-mm-yyyy'));
    END;
/


In the above code snippet, we first declare a date variable v_date and assign it the initial date to which we want to add business days. We then specify the number of business days to add in the variable v_business_days_number.


Next, we use a FOR loop to iterate over each business day and increment the date by finding the next weekday using the NEXT_DAY function.


Finally, we print the resulting date after adding the specified number of business days.


You can modify the code snippet to suit your specific requirements, such as changing the initial date, number of business days to add, or the desired weekday for business days.


How to add months to a date while skipping weekends in Oracle?

One approach to add months to a date while skipping weekends in Oracle is to create a PL/SQL function that calculates the new date by adding the specified number of months and skipping weekends.


Here is an example of how you can achieve this:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
CREATE OR REPLACE FUNCTION add_months_skip_weekends(p_start_date IN DATE, p_months IN NUMBER)
RETURN DATE
IS
    v_new_date DATE := p_start_date;
BEGIN
    FOR i IN 1..p_months LOOP
        v_new_date := ADD_MONTHS(v_new_date, 1);
        
        -- Skip weekends (Saturday and Sunday)
        WHILE TO_CHAR(v_new_date, 'DY', 'NLS_DATE_LANGUAGE=AMERICAN') IN ('SAT', 'SUN') LOOP
            v_new_date := v_new_date + 2; -- Skip to next Monday
        END LOOP;
    END LOOP;
    
    RETURN v_new_date;
END;
/


You can use this function to add months to a given date while skipping weekends like this:

1
2
SELECT add_months_skip_weekends(TO_DATE('2022-06-15', 'YYYY-MM-DD'), 2) AS new_date
FROM dual;


This will return a new date that is 2 months after '2022-06-15', skipping weekends.


How to add seconds and nanoseconds to a date in Oracle?

To add seconds and nanoseconds to a date in Oracle, you can use the INTERVAL data type to specify the amount of time you want to add. Here's how you can do it:

1
2
SELECT SYSDATE + INTERVAL '10' SECOND + INTERVAL '100000000' NANOSECOND
FROM dual;


In this example, we are adding 10 seconds and 100,000,000 nanoseconds to the current date and time retrieved using the SYSDATE function. You can replace the values with your desired number of seconds and nanoseconds.


Alternatively, you can use the TIMESTAMP data type to add seconds and nanoseconds to a date in Oracle:

1
2
SELECT SYSTIMESTAMP + INTERVAL '10' SECOND + INTERVAL '100000000' NANOSECOND
FROM dual;


This will add 10 seconds and 100,000,000 nanoseconds to the current timestamp. Again, you can adjust the values to suit your specific requirements.

Facebook Twitter LinkedIn Telegram Whatsapp Pocket

Related Posts:

To convert a date in PHP, you can use the built-in date() function along with various format characters to specify the desired output format. Here are the steps to convert a date:First, you need to have the date you want to convert. This can be a static date o...
To import a CSV file into a remote Oracle database, you can use SQLLoader, Oracle Data Pump, or Oracle SQL Developer. SQLLoader is a command-line tool that loads data from external files into Oracle databases. Oracle Data Pump is a feature of Oracle Database t...
To convert a date to a datetime in Oracle, you can simply cast the date as a timestamp using the TO_TIMESTAMP function. This function takes the date as an argument and converts it to a timestamp datatype. For example, if you have a date column named 'date_...