Datediff Function in Oracle

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

How to use datediff equivalent in Oracle with YYYYMMDD formatted number?

Simple subtraction in Oracle:

SELECT TO_DATE('20080805','YYYYMMDD') - TO_DATE('20080605','YYYYMMDD')
  FROM DUAL;

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.

Slow query when using datediff function in Oracle

I created a function named datediff as a datediff function in SQL, sir

Don't use custom functions as they prevent Oracle from using an index on the column; instead just compare the column to the static values:

SELECT *
FROM   Department
WHERE  flag = 1
AND    DepartmentNum IN (10,4)
AND    status IN (0,-1,100)
AND    datequit > DATE '2019-09-30' + INTERVAL '9' DAY

or

AND    datequit > DATE '2019-09-30' + NUMTODSINTERVAL( 9, 'DAY' )

or

AND    datequit > DATE '2019-09-30' + 9

Here is my datediff function code

...
WHEN 'DD' THEN RETURN ROUND(TRUNC(P_END_DATE,'DD') - TRUNC(P_START_DATE,'DD'),0);
...

If you want to do an equivalent comparison to using TRUNC to ignore the time components then change from using greater-than comparison to using greater-than-or-equal-to and add one time unit (day in your example) to the expected difference. For example:

SELECT *
FROM   Department
WHERE  flag = 1
AND    DepartmentNum IN (10,4)
AND    status IN (0,-1,100)
AND    datequit >= DATE '2019-09-30' + INTERVAL '10' DAY

Oracle date difference to get number of years

I'd use months_between, possibly combined with floor:

select floor(months_between(date '2012-10-10', date '2011-10-10') /12) from dual;

select floor(months_between(date '2012-10-9' , date '2011-10-10') /12) from dual;

floor makes sure you get down-rounded years. If you want the fractional parts, you obviously want to not use floor.

How to calculate difference between two dates in oracle 11g SQL

There is no DATEDIFF() function in Oracle. On Oracle, it is an arithmetic issue

select DATE1-DATE2 from table 

Using DateDiff() in Oracle

Why do you believe that it is not a good idea to subtract two dates to get the number of days between them? That's certainly the standard way to do that sort of date arithmetic in Oracle.

DateDiff is not a function that exists in Oracle. I know it exists in SQL Server. You could, of course, write your own function and call that

CREATE OR REPLACE FUNCTION dateDiff( p_dt1 IN DATE,
                                     p_dt2 IN DATE )
  RETURN NUMBER
IS
BEGIN
  RETURN p_dt1 - p_dt2;
END;

It's not obvious, though, what benefit you derive from doing this rather than simply continuing to subtract the two dates.

---

Related Topics