Is there a way to use the following function from MSSQL in Oracle SQL Developer
create function fnDays2NextEvent(@birthdate datetime, @today datetime)
returns integer
as
begin
declare @birthday datetime
set @birthday = @birthdate
while @birthday < @today set @birthday = dateadd(yy, 1, @birthday)
return datediff(dd,@today,@birthday)
end;
You need to create the function in Oracle similar to this one.
Like the following:
CREATE OR REPLACE FUNCTION FNDAYS2NEXTEVENT (
BIRTHDATE IN DATE,
TODAY IN DATE
) RETURN INT AS
BEGIN
RETURN BIRTHDATE
+ CEIL(MONTHS_BETWEEN(TODAY, BIRTHDATE) / 12)
* INTERVAL '1' YEAR - TODAY + 1;
END;
/
Then, You will be able to use it.
SQL> SELECT FNDAYS2NEXTEVENT(DATE'1991-07-20',SYSDATE) FROM DUAL;
FNDAYS2NEXTEVENT(DATE'1991-07-20',SYSDATE)
------------------------------------------
161
SQL> SELECT SYSDATE + 161 FROM DUAL;
SYSDATE+1
---------
20-JUL-20
SQL>
I don't know if this is what you require, Do comment in case of any discrepancy in the answer and expectation.
Cheers!!
If you are looking for the oracle equivalent, then try this:
create or replace function fnDays2NextEvent(birthdate date, today date)
return number
is
begin
return trunc(birthdate)- trunc(today) ;
end fnDays2NextEvent;
test
select fnDays2NextEvent(to_date('02/14/2020','MM/DD/YYYY'),sysdate) from dual
the function could look like:
CREATE OR REPLACE function fnDays2NextEvent(birthdate DATE, today DATE)
returns NUMBER
AS
v_bd DATE;
begin
v_bd := birthdate;
while v_bd < today
LOOP
v_bd = ADD_MONTHS(v_bd,12);
END LOOP;
RETURN today - v_bd;
end;
this code is not optimized and is 1 to 1 migration from your code
