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:
This code I'm including, is using a date parameter passed to the function to later be used on the variable assignment:
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.
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
ENDThis 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
ENDThe 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.
Your second version is calling some additional functions using that @dt as parameter:
SELECT @startFiscalMonth = dbo.RG_GetFiscalMonthStart
@endFiscalMonth = dbo.RG_GetFiscalMonthEnd(@
@fiscalYearNumber = dbo.RG_GetFiscalYearName(@
@fiscalMonthNumber = dbo.RG_GetFiscalMonthKeyFi
Are you sure those are all working fine? Have they been tested separately?
SELECT @startFiscalMonth = dbo.RG_GetFiscalMonthStart
@endFiscalMonth = dbo.RG_GetFiscalMonthEnd(@
@fiscalYearNumber = dbo.RG_GetFiscalYearName(@
@fiscalMonthNumber = dbo.RG_GetFiscalMonthKeyFi
Are you sure those are all working fine? Have they been tested separately?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
vadimrapp1: I'm fairly sure you can, try this for example: print getdate()
(Before my initial post I also tested with a UDF as I wasn't sure either :))
(Before my initial post I also tested with a UDF as I wasn't sure either :))
@ValentinoV - I said "as parameters" - meaning parameters of stored procedures and UDF's.
create proc aaa(@d datetime) as select 1
exec aaa getdate()
You'll get the same error.
create proc aaa(@d datetime) as select 1
exec aaa getdate()
You'll get the same error.
@vadimrapp1: agreed, with SPs you'll have that issue, but the table-valued function seems to work differently. If you try the following, you'll notice it will work:
create function bbb(@d datetime)
returns @t table(dt datetime)
as
begin
insert into @t select @d return;
end
select * from bbb(GETDATE());
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I did some research in Books Online; it appears that in SQL Server 2000 the syntax to call UDF was not formally defined at all; in SQL Server 2012 UDF is at least mentioned as table_source (http://msdn.microsoft.com/en-us/library/ms177634) , but the parameters are still not mentioned. I sent bug report via Connect.
> I sent bug report via Connect.
https://connect.microsoft.com/SQLServer/feedback/details/753437/books-online-absent-formal-syntax-to-call-udf
interested parties can monitor the result.
https://connect.microsoft.com/SQLServer/feedback/details/753437/books-online-absent-formal-syntax-to-call-udf
interested parties can monitor the result.
"I sent bug report via Connect. " > +1
"+1" - feel free to vote!
I did (hence the 2). Or what do you mean? As it's a comment related to documention, there's not really something to reproduce so I didn't click the "I can too" link.
Great then - thanks! " I can too" could mean that you too could not find the definition in books online.
ASKER
Hello to everyone that posted a comment related to this question. Thank you for your recommendations and help.
I found this article whilst trying to find a solution to my problem:
How do I use GETDATE() within a User-Defined Function (UDF)?
So, based on that, I made the following changes:
I found this article whilst trying to find a solution to my problem:
How do I use GETDATE() within a User-Defined Function (UDF)?
So, based on that, I made the following changes:
1. Create a View which basically gives the current date.
SELECT GETDATE() AS CurrentDate2. Remove the GETDATE() parameter to be passed, from the function call
3. Declare new datetime variable within function
DECLARE @wrkdt datetime4. Set date variable to the returned date value from the new view
SET @wrkdt = (SELECT currentdate FROM RG_CurrentDate)5. Call the function without passing any parameter
select * from [dbo].[Test_Shipping_Status] ()ASKER
I shared 400 points to vadimrapp1 because his solutions was the closest to what I ended up doing. Thank you.<br /><br />And I shared 100 points to ValentinoV, because I think he shared some of his time trying to figure this out as well, may be I did not use his approach but his kindness to help is appreciated. Thank you. <br /><br />I hope by sharing the points and the amounts, I have not offended anyone.<br /><br />Best regards,<br /><br />Metropia.
sure, that's one of the many ways. Or you as well could set the variable before using the query, as I posted in my first comment
And indeeed... My comment asked : "if it is going to be getdate() then why does it need to be a parameter"
The theory being that getdate() was being used elsewhere hence the request to try as a string... GETDATE should be able to be used as the input parameter (in SQL2000), but can not be used inside the function. So, isolating the parameter would disclose other problems.
Anyway, I reckon could have quickly suggested alternatives...
The theory being that getdate() was being used elsewhere hence the request to try as a string... GETDATE should be able to be used as the input parameter (in SQL2000), but can not be used inside the function. So, isolating the parameter would disclose other problems.
Anyway, I reckon could have quickly suggested alternatives...
> "if it is going to be getdate() then why does it need to be a parameter"
Because you can't use getdate() in UDF.
> hence the request to try as a string.
Constant string would certainly work, like any other constant, but the purpose was to pass an expression - which does not work. select dbo.myudf(cast(getdate() as varchar(50)) would result in the same syntax error.
Because you can't use getdate() in UDF.
> hence the request to try as a string.
Constant string would certainly work, like any other constant, but the purpose was to pass an expression - which does not work. select dbo.myudf(cast(getdate() as varchar(50)) would result in the same syntax error.
@vadimrapp1,
I seem to recall that getdate() as a parameter is fine - the use of non-deterministic functions within an UDF is the real problem (which might include the "fiscal..." functions as noticed by ValentinoV as well).
The idea of using a string was an attempt to try to identify / isolate the real source of the problem, it was not intended as the solution.
As shown in the link provided by the Asker (amongst many other examples), that author has an example where getdate() is being used as a parameter, which fits with my understanding.
Given the use of other functions to determine the "fiscal..." periods, I was going to suggest a calendar table (or similar) and try to get something closer to an inline table function to avoid the performance problems often associated with functions.
Alternatively, Day(), Month() and Year() are deterministic functions and might have helped solve part of the problem. datepart, datename(dw), getdate, getutcdate are all non-deterministic and wont work.
Admittedly, some of my initial posts have double meaning, but all for an attempt at finding the real underlying problem.
Without doubt, the use of non-deterministic functions within a SQL2000 database is the root of the problem (and maybe I should have said that first up).
If you have a SQL2000 server lying around, the following *should* work because all the functions being used are deterministic, and decided to return that globally accepted date as a string which could be used almost anywhere by way of example only :
I seem to recall that getdate() as a parameter is fine - the use of non-deterministic functions within an UDF is the real problem (which might include the "fiscal..." functions as noticed by ValentinoV as well).
The idea of using a string was an attempt to try to identify / isolate the real source of the problem, it was not intended as the solution.
As shown in the link provided by the Asker (amongst many other examples), that author has an example where getdate() is being used as a parameter, which fits with my understanding.
Given the use of other functions to determine the "fiscal..." periods, I was going to suggest a calendar table (or similar) and try to get something closer to an inline table function to avoid the performance problems often associated with functions.
Alternatively, Day(), Month() and Year() are deterministic functions and might have helped solve part of the problem. datepart, datename(dw), getdate, getutcdate are all non-deterministic and wont work.
Admittedly, some of my initial posts have double meaning, but all for an attempt at finding the real underlying problem.
Without doubt, the use of non-deterministic functions within a SQL2000 database is the root of the problem (and maybe I should have said that first up).
If you have a SQL2000 server lying around, the following *should* work because all the functions being used are deterministic, and decided to return that globally accepted date as a string which could be used almost anywhere by way of example only :
CREATE FUNCTION udf_EOM (@d DATETIME)
RETURNS char(10)
as
begin
return (select convert(char(10),(dateadd(month,1+datediff(month,0,@d),0) - 1),120))
end
GO
Select dbo.udf_EOM(getdate())
GO
Mark, I think the problem is not in deterministic/non-determin
I.e. you can pass expression as parameter of scalar UDF in both 2k and 2008, but in 2K you can't pass it to table-valued UDF. This was implemented somewhere later. When this was changed, and more generally the formal syntax of calling UDF is still completely undocumented, which is what I reported on Connect.
Once you can pass expressions, you can pass whatever you want, whether it's 1+1 or getdate(), or convert() or anything else.
And you still can't pass them to stored procedures, which hardly makes sense given that for udf's it's implemented.
sql2k:
create function scalar_udf(@i int) returns int as begin return @i end
go
select dbo.scalar_udf(1+1)
success.
create function table_udf(@i int) returns @t table(c int) as begin
insert into @t select 1;return;end
go
select * from dbo.table_udf(1+1)
error: Line 1: Incorrect syntax near '+'.
sql2008:
create function scalar_udf(@i int) returns int as begin return @i end
go
select dbo.scalar_udf(1+1)
success
create function table_udf(@i int) returns @t table(c int) as begin
insert into @t select 1;return;end
go
select * from dbo.table_udf(1+1)
success
create function scalar_udf(@i int) returns int as begin return @i end
go
select dbo.scalar_udf(1+1)
success.
create function table_udf(@i int) returns @t table(c int) as begin
insert into @t select 1;return;end
go
select * from dbo.table_udf(1+1)
error: Line 1: Incorrect syntax near '+'.
sql2008:
create function scalar_udf(@i int) returns int as begin return @i end
go
select dbo.scalar_udf(1+1)
success
create function table_udf(@i int) returns @t table(c int) as begin
insert into @t select 1;return;end
go
select * from dbo.table_udf(1+1)
success
I.e. you can pass expression as parameter of scalar UDF in both 2k and 2008, but in 2K you can't pass it to table-valued UDF. This was implemented somewhere later. When this was changed, and more generally the formal syntax of calling UDF is still completely undocumented, which is what I reported on Connect.
Once you can pass expressions, you can pass whatever you want, whether it's 1+1 or getdate(), or convert() or anything else.
And you still can't pass them to stored procedures, which hardly makes sense given that for udf's it's implemented.
@vadimrapp1,
OK, understand what you are saying now (maybe I should have read the connect issue).
And it looks like you have access to SQL2000 (which I removed only two weeks ago to make room for SQL2012).
Just to complete my own understanding, does that extend to inline table valued functions ?
e.g.
OK, understand what you are saying now (maybe I should have read the connect issue).
And it looks like you have access to SQL2000 (which I removed only two weeks ago to make room for SQL2012).
Just to complete my own understanding, does that extend to inline table valued functions ?
e.g.
CREATE FUNCTION udf_EOM (@d DATETIME)
RETURNS table
as
return (select convert(char(10),(dateadd(month,1+datediff(month,0,@d),0) - 1),120) as EOM)
GO
Select * from udf_EOM(getdate())
GO
No, incorrect syntax.
I'm indeed still running sql2000. When I was beta-testing 2008, I put my production ERP database on it, and in 15 minutes already had a query that takes 5 seconds in sql2k and 2 minutes in 2008. Filed bug report. Microsoft confirmed with the comment "This is something to think about in future versions". My natural follow-up question "maybe it's easier to look in the past ones?" went unanswered...
I'm indeed still running sql2000. When I was beta-testing 2008, I put my production ERP database on it, and in 15 minutes already had a query that takes 5 seconds in sql2k and 2 minutes in 2008. Filed bug report. Microsoft confirmed with the comment "This is something to think about in future versions". My natural follow-up question "maybe it's easier to look in the past ones?" went unanswered...
> No, incorrect syntax.
actually the right answer is "yes" i.e. inline table-valued functions in sql2k don't accept expressions as parameters as well.
actually the right answer is "yes" i.e. inline table-valued functions in sql2k don't accept expressions as parameters as well.
Ta muchly... Always good to get to the real root of the problem.
And quite happy not needing to deal with SQL2000 anymore (and have had to resolve similar to your 5 second > 2 minute queries in escaping sql2000).
Still, re-reading my first post, was a little disappointed with no feedback.
Typically when resolving fiscal periods I tend to create the calendar table, which often bypasses the problem without actually having to deal with the limitations of expressions in SQL2000 table valued functions.
Thanks to all (particularly Vadim) for indulging the conversation a little beyond the scope...
And quite happy not needing to deal with SQL2000 anymore (and have had to resolve similar to your 5 second > 2 minute queries in escaping sql2000).
Still, re-reading my first post, was a little disappointed with no feedback.
Typically when resolving fiscal periods I tend to create the calendar table, which often bypasses the problem without actually having to deal with the limitations of expressions in SQL2000 table valued functions.
Thanks to all (particularly Vadim) for indulging the conversation a little beyond the scope...
With these types of things, it is sometimes easier to take a step back and first check to see if your logic is all OK, then the next step is to worry about how to use "today's date"
So, just as a test, what happens if you try :
select * from [dbo].[Test] ('2012-07-12')
the string '20120712' or '2012-07-12' should automatically convert into a datetime.
next thing to consider is the parameter itself - if it is going to be getdate() then why does it need to be a parameter ? You could use it indirectly. So, maybe instead of passing getdate() as a parameter, could we use some other kind of flag (or maybe number of days) to indicate which period to use.
Anyway, lets try that string first up.