CODEWITHSUNDEEP
sql-serveroracle-databaseplsqldatabase-migration
N4meless
N4meless

Reputation: 1

functions from MSSQL to Oracle SQL

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;

Upvotes: 0

Views: 129

Answers (3)

Popeye
Popeye

Reputation: 35920

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!!

Upvotes: 1

hotfix
hotfix

Reputation: 3396

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

Upvotes: 0

OldProgrammer
OldProgrammer

Reputation: 12169

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

Upvotes: 0

Related Questions