Equivalent Function for Dateadd() in Oracle

DateAdd function in Oracle SQL

In Oracle, you would use:

select sysdate - interval '28' day

Or, if you prefer:

select sysdate - 4 * interval '7' day

DateAdd function in pl/sql

It appears there's not many solutions :

PL/SQL allows you to perform arithmetic operations directly on date variables. You may add numbers to a date or subtract numbers from a date. To move a date one day in the future, simply add 1 to the date as shown below:

hire_date + 1

You can even add a fractional value to a date. For example, adding 1/24 to a date adds an hour to the time component of that value. Adding 1/(24*60) adds a single minute to the time component, and so on.

Dateadd and Datediff function in oracle

The best thing to do in this case is to use Oracle's MONTHS_BETWEEN() function.

Instead of:

datediff('QUARTER', pr.StartDate, SYSDATE)

you would use:

MONTHS_BETWEEN(pr.startdate, SYSDATE) / 3

and instead of:

datediff('MONTH', pr.StartDate, SYSDATE)

you would use:

MONTHS_BETWEEN(pr.startdate, SYSDATE)

Keep in mind that MONTHS_BETWEEN() will return fractions of months, so use TRUNC() or ROUND() if you need an integer number.

Of course, if you need days instead of months, you can simply subtract one date from another, e.g., SYSDATE - pr.startdate or vice-versa.

If you need to add days to a date, you can simply to this:

pr.startdate + 1 -- where 1 is the number of days

And if you need to add or subtract months, use the ADD_MONTHS() function - unlike INTERVALs this function is safe to use in leap years.

Hope this helps.

migrate dateadd and datepart from sybase to oracle

I'm not a Sybase person, but I'm working on the assumption from the code that you want the start of the month that was 13 months ago.

In which case, its:

SQL> select trunc(add_months(sysdate,-13),'MM') from dual;

TRUNC(ADD
---------
01-OCT-20

Oracle (10g) equivalent of DATEADD(weekday, -3, GETDATE())

It looks like you need to create a UDF.

CREATE OR REPLACE FUNCTION business_date (start_date DATE, 
days2add NUMBER) RETURN DATE IS
Counter NATURAL := 0;
CurDate DATE := start_date;
DayNum POSITIVE;
SkipCntr NATURAL := 0;
Direction INTEGER := 1; -- days after start_date
BusinessDays NUMBER := Days2Add;
BEGIN
IF Days2Add < 0 THEN
Direction := - 1; -- days before start_date
BusinessDays := (-1) * BusinessDays;
END IF;

WHILE Counter < BusinessDays LOOP
CurDate := CurDate + Direction;
DayNum := TO_CHAR( CurDate, 'D');

IF DayNum BETWEEN 2 AND 6 THEN
Counter := Counter + 1;
ELSE
SkipCntr := SkipCntr + 1;
END IF;
END LOOP;

RETURN start_date + (Direction * (Counter + SkipCntr));
END business_date;

Courtesy of Larry Benton, from here.

When I enter a dateadd or datediff code i get this error all the time ORA-00904 DATEADD INVALID IDENTIFIER.

The typical way of doing this in Oracle would be:

DELETE FROM patient
WHERE dis_date < TRUNC(ADD_MONTHS(SYSDATE, -7*12));

The reason I suggest using ADD_MONTHS() instead of year intervals is that ADD_MONTHS() is leap-year safe.

DATEDIFF function in Oracle

In Oracle, you can simply subtract two dates and get the difference in days. Also note that unlike SQL Server or MySQL, in Oracle you cannot perform a select statement without a from clause. One way around this is to use the builtin dummy table, dual:

SELECT TO_DATE('2000-01-02', 'YYYY-MM-DD') -  
TO_DATE('2000-01-01', 'YYYY-MM-DD') AS DateDiff
FROM dual


Related Topics



Leave a reply



Submit