Menu
I want to subtract "X" days and "X" minutes from sysdate, where the days and minutes are an integer as input parameter. For instance, 10 days and 5 minutes.
I found many examples to subtract either minutes or hours but not a combination of days and minutes.
select sysdate - 5 / 24 / 60 from dual -- will retrieve sysdate minus 5 minutes.
--What about the days?
Thank you!
566
To calculate the difference between the timestamps in Oracle, simply subtract the start timestamp from the end timestamp (here: arrival - departure ). The resulting column will be in INTERVAL DAY TO SECOND . The first number you see is the number of whole days that passed from departure to arrival .
If you only care about weekends and ignore holidays, you can use the following formula: DATEADD(DAY, -7*(@bdays / 5) + @bdays % 5, @start_date) . Then if the result falls on Saturday subtract 1 day, if it falls on Sunday subtract 2 days.
FUNCTION get_minute(p_date DATE) RETURN NUMBER IS BEGIN IF p_date IS NOT NULL THEN return EXTRACT(MINUTE FROM TO_TIMESTAMP(to_char(p_date,'DD-MON-YYYY HH:MI:SS'),'DD-MON-YYYY HH24:MI:SS')); ELSE RETURN 0; END IF; END get_minute; oracle. date. plsql.
Use an interval literal:
SELECT SYSDATE - INTERVAL '10 00:05' DAY(2) TO MINUTE
FROM DUAL
Or:
SELECT SYSDATE - INTERVAL '10' DAY - INTERVAL '5' MINUTE
FROM DUAL
Or just use arithmetic:
SELECT SYSDATE - 10 /* Days */ - 5 / 24 /60 /* Minutes */
FROM DUAL
Or use NUMTODSINTERVAL:
SELECT SYSDATE - NUMTODSINTERVAL( 10, 'DAY' ) - NUMTODSINTERVAL( 5, 'MINUTE' )
FROM DUAL
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
