Error: "ORA-00904: "DATEADD": invalid identifier" when executing SQL with 'DateAdd' functions. (4348941)

Error: "ORA-00904: "DATEADD": invalid identifier" when executing SQL with 'DateAdd' functions.

Error received when executing a SQL statement with the Oracle DateAdd function:

"ORA-00904: "DATEADD": invalid identifier"

To reproduce this error, execute Select DateADD(dd , -10, to_date(05/APR/2004)) from dual in a SQL Editor window.

The DateAdd function does not exist in the database by default.

Verify that the DateAdd function exists in the database. If the DateAdd function does not exist in the database, then it will need to created.

Here is the script needed to create the DateAdd function.

Create or replace Function DateADD(vchDatePart varchar , intOP in int, dt date)
/***********************************************************************************
--GEO--C-- GAjoseph
--GEO--E-- Select DateADD(dd , -10, sysdate) from dual
--GEO--E-- Select DateADD(dd , 30, to_date(31-FEB-2003)) from dual
--GEO--E-- Select dateadd(m, 2 , sysdate), to_char(DateADD ( h , 3 ,
to_date(01-JAN-2003 10:33:44 , dd-MON-YYYY HH:MI:SS) ) , dd-MON-YYYY
HH:MI:SS )
, DateADD ( h , 3 , to_date(01-JAN-2003 10:33:44 ,
dd-MON-YYYY HH:MI:SS) ) From dual;
--GEO--C-- Returns a new datetime value based on adding an interval to the
specified
date.
--GEO--Ex--
Is the parameter that specifies on which part of the date to
return a new value. The table lists the dateparts and abbreviations
recognized by Microsoft� SQL Server�.
Datepart Abbreviations
Year yy, yyyy
Month mm, m
Day dd, d
Hour hh
minute mi, n
second ss, s
************************************************************************************/
return date
as
dd int;
mm int;
yyyy int;
hh int;
NN int;
SS int;
v date;
lintOP int;
Begin
lintOP := intOP;
--GEO--C-- INcreament Days
if upper(vchDatePart) like D% then
return dt + intOP;
end if;
dd := to_number(to_Char(dt,dd));
mm := to_number(to_Char(dt,MM));
yyyy:= to_number(to_Char(dt,yyyy));
HH := to_number(to_Char(dt, HH));
NN := to_number(to_Char(dt, MI));
SS := to_number(to_Char(dt, SS));
--GEO--C-- INcreament Year
if upper(vchDatePart) like Y% then
yyyy:= yyyy+ lintOP;
end if;
--GEO--C-- INcreament Month.
if upper(vchDatePart) like M% then
yyyy:= yyyy+round(lintOP/12);
mm := mm+mod(lintOP,12);
end if;-->MM
if upper(vchDatePart) like H% then
dd := dd + round(lintOP/24);
hh := hh + mod(lintOP,24);
end if;--> hh
if upper(vchDatePart) like N% then
dd := dd + round(lintOP/(24*60));
hh := hh + round(lintOP/60);
NN := NN + mod(lintOP , 60);
end if;--> MInutes
if upper(vchDatePart) like S% then
dd := dd + round(lintOP/(24*60*60));
hh := hh + round(lintOP/60*60);
NN := NN + round(lintOP/60);
NN := NN + MOD(lintOP,60);
end if;--> SS
v := LAST_DAY(to_date(01/||to_char(mm,09)||/|| to_char(yyyy,
0009),dd/mm/yyyy));
if dd > to_number(to_Char(v,DD)) then
dd := to_number(to_Char(v,DD));
end if;
return to_date(lpad(dd,2,0)||to_char(mm,09)||/|| to_char(yyyy,
0009)|| ||lpad(hh,2,0)||:||lpad(NN,2,0)||:||lpad(SS,2,0),
dd/mm/yyyy HH24:MI:SS) ;
exception when others then return null ;
End;