using function in query

Question

Post reply

using function in query

@DJ

SSCommitted

Points: 1989
More actions
June 28, 2011 at 7:24 am

#148246

Hi
I trying to use a function within a query. But it does not seem to work.
use datamarts;
CREATE FUNCTION Getweek (@DATE datetime)
RETURNS int
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @weeknumber int;
SET @weeknumber= DATEPART(wk,@DATE)+1
-DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104');
--Special cases: Jan 1-3 may belong to the previous year
IF (@weeknumber=0)
SET @weeknumber=dbo.weeknumber(CAST(DATEPART(yy,@DATE)-1
AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1;
--Special case: Dec 29-31
IF ((DATEPART(mm,@DATE)=12) AND
((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
SET @weeknumber=1;
RETURN(@weeknumber);
END;
GO
select dbo.Getweek(CONVERT(DATETIME,date ,101))
from datamarts.dbo.[system calender]
The system calendar only holds date as character. I also have it as date2 = datetime.
Runing yields the error
Error: Cannot find either column "dbo" or the user-defined function or aggregate "dbo.weeknumber", or the name is ambiguous
select dbo.Getweek(CONVERT(DATETIME,'2011-28-06' ,101)) works just fine.
Dan

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

looks like you are missing another function: dbo.weeknumber

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Dan.Humphries SSChampion Points: 12125 More actions · Answer 2

I am not sure what you are trying to do with this line but this would be where the problem is.

you do not get it when you test with a set date because it would fail the IF case. however I suspect you are being oassed into the if statement at some point in you select and that triggers the error.

Either way I would say this syntax seems hinky

SET @weeknumber=dbo.weeknumber(CAST(DATEPART(yy,@DATE)-

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 3

Personally i would use a calendar table here.

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

Clear Sky SQL
My Blog[/url]

LutzM SSC Guru Points: 107049 More actions · Answer 4

The code seems to be based on the BOL example A for "CREATE FUNCTION".

In the original code, the function itself is called inside the function.

In your case this would be

SET @weeknumber=dbo.Getweek(CAST(DATEPART(yy,@DATE)-1

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]