Add Days Oracle SQL

How to Add days to a date column along with time in SQL

When you use + 1, the timestamp is converted to a date. That is why you are only seeing the date (the default representation of a date does not include the time component although it is there).

Instead, use interval:

select read_time, read_time + interval '1' day
from meter_read_alert;

PS. I learned something from this. Interesting question.

Adding days to a date in a table - SQL

It is simple, try the below SQL

Select TO_CHAR(DateQualified + 15,'Month fmDD, YYYY') as "Reminder Date"
FROM Qualified;

Oracle query- How subtract or add days in the given date

If I got it right, you need to change the upper date of "BETWEEN" clause.

In this case that clause might look like this

SELECT * 
From HDB.MDTA E
WHERE E.MODIFIED_DAY_KEY BETWEEN TO_CHAR(${pEND_DATE}, 'YYYYMMDD') AND TO_CHAR(${pEND_DATE} + 123, 'YYYYMMDD');

The "123" is the number of days that will get added to the pEND_DATE parameter

Oracle - Add days/months to a TIMESTAMP WITH TIME ZONE field

When you do DATED = DATED + 2, then DATED is implicitly converted to a DATE value (which does not have any time zone information) and then converted back to TIMESTAMP WITH TIME ZONE value using your current session time zone settings.

Try

UPDATE MY_TABLE SET DATED = DATED + INTERVAL '2' DAY WHERE ID = 1165;

or

UPDATE MY_TABLE SET DATED = DATED + 2 * INTERVAL '1' DAY WHERE ID = 1165;

or

UPDATE MY_TABLE SET DATED = DATED + NUMTODSINTERVAL(2, 'day') WHERE ID = 1165;

In order to add 2 months you can use

UPDATE MY_TABLE SET DATED = DATED + INTERVAL '2' MONTH WHERE ID = 1165;

resp. NUMTOYMINTERVAL(2, 'MONTH'), for example.

PL/SQL function to add +7 days to SYSDATE

You have to format the result to your specifications, as in

--create the function  
CREATE OR REPLACE FUNCTION get_date(n IN NUMBER)
RETURN DATE AS
BEGIN
RETURN SYSDATE + n;
END get_date;

--call the function
SELECT TO_CHAR(get_date(7), 'DD.MM.YYYY HH24:MI')
FROM dual;

Or your new requirement of no formatting outside the function

--create the function  
CREATE OR REPLACE FUNCTION get_date(n IN NUMBER)
RETURN VARCHAR2 AS
BEGIN
RETURN TO_CHAR(SYSDATE + n,'DD.MM.YYYY HH24:MI');
END get_date;

--call the function
SELECT get_date(7)
FROM dual;


Related Topics



Leave a reply



Submit