SQL to Oracle Date Issue

Question

Currently in middle of migration from SQL Server to Oracle. Whats the best practices that i should applied across?

And we also encounter some problem like the dateadd functions not working in oracle.

MSSQL Code

USE [TEST]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[GET_MONTHS_LAST_DAY](@MON int)
RETURNS DATETIME 
AS
BEGIN
RETURN DATEADD(dd, -DAY(DATEADD(m,1,getdate())), DATEADD(m,-MON+1,datediff(dd,0,getdate()))) 
END

Converted Oracle

create or replace

FUNCTION GET_MONTHS_LAST_DAY
(
  v_MON IN NUMBER
)
RETURN DATE
AS
BEGIN
   RETURN utils.dateadd('DD', -utils.day_(utils.dateadd('M', 1, SYSDATE)), utils.dateadd('M', -v_MON + 1, utils.datediff('DD', 0, SYSDATE)));
END;

Any idea why i cannot compile the oracle functions? The only thing i see here is the dateadd functions are not available in oracle. Thanks.

Answer 1

Your script looks like Oracle SQL Developer automatic conversion script.

You need generate package utils. Yo can use button "generate utils package" ( 2nd button in capture)in util window.