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 October 2024
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.
