SQL User Defined Function Within Select - Again

Question

Firstly apologies - I know there are numerous threads on this subject however I can't find an answer to my question.

I have a UDF to work out business days between two dates. I am using SQL Server 2008.

CREATE FUNCTION dbo.fn_CountWeekDays
(
    @fromdate Datetime,
    @todate Datetime
)
RETURNS TABLE AS RETURN
(

SELECT
   (DATEDIFF(dd, @fromdate, @todate) + 1)
  -(DATEDIFF(wk, @fromdate, @todate) * 2)
  -(CASE WHEN DATENAME(dw, @fromdate) = 'Sunday' THEN 1 ELSE 0 END)
  -(CASE WHEN DATENAME(dw, @todate) = 'Saturday' THEN 1 ELSE 0 END)
  As NoOfWeekDays

)

I am trying to call it in a select statement along with another table:

select dbo.fn_CountWeekDays(a.date1,a.date2) as workdays
from alex_datetest a

However I am getting the error:

Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fn_CountWeekDays", or the name is ambiguous.

If I drop the dbo. then I get this:

'fn_CountWeekDays' is not a recognized function name.

Does anyone have any ideas why I can't reference this function?

Thanks in advance

Alex

Your function returns a table, but you're referencing it as if it returned a scalar value. — TZHX
– TZHX, Commented Aug 3, 2017 at 10:37
You are using table value function, all you need it to change the RETURN to the datatype you want to use, for you it will be int — Bartosz X
– Bartosz X, Commented Aug 3, 2017 at 10:39

Bartosz X · Accepted Answer · 2017-08-03 10:41:57Z

1

As I put in the comment, alter your function to be:

CREATE FUNCTION dbo.fn_CountWeekDays
(
    @fromdate Datetime,
    @todate Datetime
)
RETURNS int
AS
BEGIN DECLARE @int int

SET @int = 
   (DATEDIFF(dd, @fromdate, @todate) + 1)
  -(DATEDIFF(wk, @fromdate, @todate) * 2)
  -(CASE WHEN DATENAME(dw, @fromdate) = 'Sunday' THEN 1 ELSE 0 END)
  -(CASE WHEN DATENAME(dw, @todate) = 'Saturday' THEN 1 ELSE 0 END);

  RETURN @int
END

answered Aug 3, 2017 at 10:41

Bartosz X

2,86828 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2017-08-03 10:37:57Z

0

You have a table valued function, so you want to use apply:

select cwd.*
from alex_datetest a cross apply 
     dbo.fn_CountWeekDays(a.date1,a.date2) cwd

If you want to name the column:

select cwd.workdays
from alex_datetest a cross apply 
     dbo.fn_CountWeekDays(a.date1,a.date2) cwd(workdays)

answered Aug 3, 2017 at 10:37

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

Kannan Kandasamy · Accepted Answer · 2017-08-03 10:39:49Z

0

Since it is returning table you might require to use cross apply as below:

Select * from yourtable a
   cross apply dbo.fn_CountWeekDays(a.date1,a.date2) f

answered Aug 3, 2017 at 10:39

Kannan Kandasamy

14.1k3 gold badges28 silver badges40 bronze badges

1 Comment

Alexander Over a year ago

Thank you so much - Works perfectly. I've been puzzling over this for hours

Collectives™ on Stack Overflow

SQL User Defined Function Within Select - Again

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related