UDF - passing GETDATE() when calling function

Hi,

I would like to make this question, short and clear, I would struggle to do this, but please bear with me. I am in need of help, stumbling with this problem for more than two days and I do not seem to make any advances.

I am trying to call a UDF that returns a table, by passing GETDATE() as the argument.

i.e.

select * from [dbo].[Test] (getdate())

When I do this, I get an error: Line 1: Incorrect syntax near '('.

There are several variables that need the date value from get date
I have tested each variable by hard-coding the value.
I have tested each part that makes up the entire function.

This piece of code that I am including, is the function being tested using hard-coded date values, year value, month number value, and no passing parameter:

GO

SET QUOTED_IDENTIFIER ON
GO


ALTER FUNCTION [dbo].[Alex_Test] ()


RETURNS @Alex_Test TABLE
	(
		 ProductCode varchar(10)
		,ProductDescription varchar(50)
		,TotalActualShipFeet DECIMAL(14,4)
		,TotalActualShip$$ DECIMAL(14,4)
	)	
AS
BEGIN

DECLARE @startFiscalMonth datetime,
		@endFiscalMonth datetime,
		@fiscalYearNumber int,
		@fiscalMonthNumber int,
		@currentDateMinusOne datetime
		
SELECT	
		@startFiscalMonth = '2012-07-02', 
		@endFiscalMonth = '2012-08-05',
		@fiscalYearNumber = 2013,
		@fiscalMonthNumber = 4,
		@currentDateMinusOne = '2012-07-10'

INSERT INTO @Alex_Test

SELECT 
	 actualshipments.[Product_Code]
	,actualshipments.[Description]
	,actualshipments.[TotalActualShipFeet]
	,actualshipments.[TotalActualShip$$]
FROM 
(
	----------------------
	-- ACTUAL SHIPMENTS --
	----------------------
	SELECT top 100 percent
	-----------
	-- COL 1 --
	-----------
	 col.PRODUCT_CODE AS PRODUCT_CODE	
	-----------
	-- COL 2 --
	-----------
	,p.DESCRIPTION AS DESCRIPTION
	-----------
	-- COL 3 --
	-----------
	,SUM((CASE
			WHEN col.PRODUCT_CODE IN ('8500', '9000') THEN 0
			ELSE
				CASE
					WHEN RG_SALESFORECASTBYPRODUCT_WEBSTER.FiscalYear = @fiscalYearNumber
						AND RG_SALESFORECASTBYPRODUCT_WEBSTER.FiscalMonth = @fiscalMonthNumber
						THEN
							(CASE WHEN shl.SHIPPING_UM = 'FT' THEN shl.SHIPPED_QTY ELSE 0 END )
				End
		END)) AS 'TotalActualShipFeet'
	-----------
	-- COL 4 --
	-----------
	,SUM((CASE
		WHEN col.PRODUCT_CODE IN ('8500', '9000') THEN 0
		ELSE
			CASE
				WHEN RG_SALESFORECASTBYPRODUCT_WEBSTER.FiscalYear = @fiscalYearNumber
					AND RG_SALESFORECASTBYPRODUCT_WEBSTER.FiscalMonth = @fiscalMonthNumber
					THEN ROUND((shl.SHIPPED_QTY * shl.UNIT_PRICE), 4)
				ELSE 0
			END
		END)) AS 'TotalActualShip$$'		
	FROM dbo.SHIPPER AS sh
	INNER JOIN dbo.SHIPPER_LINE AS shl ON sh.CUST_ORDER_ID = shl.CUST_ORDER_ID AND sh.PACKLIST_ID = shl.PACKLIST_ID
	INNER JOIN dbo.CUST_ORDER_LINE AS col ON shl.CUST_ORDER_ID = col.CUST_ORDER_ID AND shl.CUST_ORDER_LINE_NO = col.LINE_NO
	INNER JOIN dbo.PRODUCT AS p ON col.PRODUCT_CODE = p.CODE
	INNER JOIN dbo.RG_SALESFORECASTBYPRODUCT_WEBSTER AS RG_SALESFORECASTBYPRODUCT_WEBSTER ON p.[DESCRIPTION] = RG_SALESFORECASTBYPRODUCT_WEBSTER.ProductCode
	WHERE sh.SHIPPED_DATE >= @startFiscalMonth AND sh.SHIPPED_DATE <= @endFiscalMonth
	GROUP BY col.PRODUCT_CODE, p.DESCRIPTION
) as actualshipments

	RETURN 
END

Select allOpen in new window

This code I'm including, is using a date parameter passed to the function to later be used on the variable assignment:

GO

SET QUOTED_IDENTIFIER ON
GO


ALTER FUNCTION [dbo].[Alex_Test] (@dt DATETIME)


RETURNS @Alex_Test TABLE
	(
		 ProductCode varchar(10)
		,ProductDescription varchar(50)
		,TotalActualShipFeet DECIMAL(14,4)
		,TotalActualShip$$ DECIMAL(14,4)
	)	
AS
BEGIN

DECLARE @startFiscalMonth datetime,
		@endFiscalMonth datetime,
		@fiscalYearNumber int,
		@fiscalMonthNumber int
		
SELECT	@startFiscalMonth = dbo.RG_GetFiscalMonthStart(@dt), 
		@endFiscalMonth = dbo.RG_GetFiscalMonthEnd(@dt),
		@fiscalYearNumber = dbo.RG_GetFiscalYearName(@dt),
		@fiscalMonthNumber = dbo.RG_GetFiscalMonthKeyFiscal(@dt)

INSERT INTO @Alex_Test

SELECT 
	 actualshipments.[Product_Code]
	,actualshipments.[Description]
	,actualshipments.[TotalActualShipFeet]
	,actualshipments.[TotalActualShip$$]
FROM 
(
	----------------------
	-- ACTUAL SHIPMENTS --
	----------------------
	SELECT top 100 percent
	-----------
	-- COL 1 --
	-----------
	 col.PRODUCT_CODE AS PRODUCT_CODE	
	-----------
	-- COL 2 --
	-----------
	,p.DESCRIPTION AS DESCRIPTION
	-----------
	-- COL 3 --
	-----------
	,SUM((CASE
			WHEN col.PRODUCT_CODE IN ('8500', '9000') THEN 0
			ELSE
				CASE
					WHEN RG_SALESFORECASTBYPRODUCT_WEBSTER.FiscalYear = @fiscalYearNumber
						AND RG_SALESFORECASTBYPRODUCT_WEBSTER.FiscalMonth = @fiscalMonthNumber
						THEN
							(CASE WHEN shl.SHIPPING_UM = 'FT' THEN shl.SHIPPED_QTY ELSE 0 END )
				End
		END)) AS 'TotalActualShipFeet'
	-----------
	-- COL 4 --
	-----------
	,SUM((CASE
		WHEN col.PRODUCT_CODE IN ('8500', '9000') THEN 0
		ELSE
			CASE
				WHEN RG_SALESFORECASTBYPRODUCT_WEBSTER.FiscalYear = @fiscalYearNumber
					AND RG_SALESFORECASTBYPRODUCT_WEBSTER.FiscalMonth = @fiscalMonthNumber
					THEN ROUND((shl.SHIPPED_QTY * shl.UNIT_PRICE), 4)
				ELSE 0
			END
		END)) AS 'TotalActualShip$$'		
	FROM dbo.SHIPPER AS sh
	INNER JOIN dbo.SHIPPER_LINE AS shl ON sh.CUST_ORDER_ID = shl.CUST_ORDER_ID AND sh.PACKLIST_ID = shl.PACKLIST_ID
	INNER JOIN dbo.CUST_ORDER_LINE AS col ON shl.CUST_ORDER_ID = col.CUST_ORDER_ID AND shl.CUST_ORDER_LINE_NO = col.LINE_NO
	INNER JOIN dbo.PRODUCT AS p ON col.PRODUCT_CODE = p.CODE
	INNER JOIN dbo.RG_SALESFORECASTBYPRODUCT_WEBSTER AS RG_SALESFORECASTBYPRODUCT_WEBSTER ON p.[DESCRIPTION] = RG_SALESFORECASTBYPRODUCT_WEBSTER.ProductCode
	WHERE sh.SHIPPED_DATE >= @startFiscalMonth AND sh.SHIPPED_DATE <= @endFiscalMonth
	GROUP BY col.PRODUCT_CODE, p.DESCRIPTION
) as actualshipments

	RETURN 
END

Select allOpen in new window

The second version is the one that returns the error.

I am using sql server 2000, and I tested the same code in sql server 2008, and I do not receive that error, in that version of sql, the function runs fine.

Thank you for all your help on this matter.