User Defined Function Help Needed

Hello All,

I hope that some one here can help. I should note that I have very limited experience in SQL. We are upgrading from a Redbrick (IBM) warehouse to a SQL server version. In an effort to avoid rewriting 3600 reports I am trying to create a user defined function in SQL that looks like a Redbrick function. Rebrick uses the function SUBSTR() where sql uses SUBSTRING(). I simply want to define a function in sql that uses SUBTRING when is sees SUBSTR.

On a separate note Redbrick also uses CURRENT_DATE (reserved in SQL). I want to get the system to see getdate instead. This is what I came up with, but it does not appear to work:

ALTER FUNCTION [dbo].[CURRENT_DATE]

)

RETURNS DATETIME

-- Declare the return variable here

--DECLARE GETDATE DATETIME

-- Add the T-SQL statements to compute the return value here

--SELECT GETDATE()

-- Return the result of the function

RETURN GETDATE()

Sample Query:

select * from umr.dbo.medicalclaimlineitem where paiddate<=dbo.CURRENT_DATE

Result:

We were close.  The correct answer is:

Again, I'm a novice here...

This is what I have so far, but it does not work:

CREATE FUNCTION dbo.substr

-- Add the parameters for the function here

@Expression varchar

@Start bigint

@Length bigint

RETURNS varchar

-- Declare the return variable here

--DECLARE @Result varchar, nvarchar, text, ntext, varbinary, image

-- Add the T-SQL statements to compute the return value here

--SELECT @Result = @Expression

-- Return the result of the function

RETURN substring(@Expression @Start @Length)

Nevermind. I got it.

CREATE FUNCTION dbo.substr

-- Add the parameters for the function here

@Expression as varchar,

@Start as bigint,

@Length as bigint

RETURNS varchar

-- Declare the return variable here

--DECLARE @Result varchar, nvarchar, text, ntext, varbinary, image

-- Add the T-SQL statements to compute the return value here

--SELECT @Result = @Expression

-- Return the result of the function

RETURN substring(@Expression, @Start, @Length)