UDF for date comparisons

1

I'm trying to create a userdefined scalar function where i subtract the number of days (eliminating weekends) from one starting point [starting] to [arrival], but these two columns are in different tables. In a long-winded query , the order# will correlate with each of these inside the respective tables:

create function [dbo].fn_daysapart
returns int
as
begin
return(select (DATEDIFF(dd,convert(datetime,t1.starting,121),t2.arrival) -
(2 * DATEDIFF(wk,convert(datetime,t1.starting,121),t2.arrival)) from tableorders t1,tabledetails t2 )
end

...but i get "incorrect syntax near the FROM keyword" error , even tho i believe i hav my parenthesis correctly positioned (and am not getting intellisense red squigglies on any of these)
??
Thanks

2

You have seven left brackets and six right brackets, unless I counted them wrong. Also, the function name needs to have a open and close bracket even if you have no parameters. So perhaps this? (I checked only the syntax, didn't check your logic)

CREATE FUNCTION [dbo].fn_daysapart ( )
RETURNS INT
AS
    BEGIN

        RETURN(
		SELECT DATEDIFF(dd,CONVERT(DATETIME,t1.starting,121),t2.arrival) - 
(2 * DATEDIFF(wk,CONVERT(DATETIME,t1.starting,121),t2.arrival)) 
	FROM tableorders t1,tabledetails t2  );
    END;
3

I'll leave it a scalar function for now, but you would get vastly greater efficiency by making it an inline-table-valued function, then using CROSS APPLY to get the results. I can help you with that if you want to change the type of function.

As to the function itself, it needs rewritten to accept the dates as parameters, not to refer to the original tables.

Here's the new code that invokes the function and the corrected function itself:

SELECT <other_cols>, dbo.fn_daysapart(t1.starting, t2.arrival) AS days_diff, <more_columns>
FROM tableorders t1
INNER JOIN tabledetails t2 ON t2.order_id = t1.order_id

CREATE FUNCTION dbo.fn_daysapart (
    @starting datetime,
    @arrival datetime
    )
RETURNS int
AS
BEGIN
RETURN (
    SELECT 
        DATEDIFF(DAY, @starting, @arrival) + 1 -
        (DATEDIFF(WEEK, @starting, @arrival) * 2) -
        CASE WHEN DATEDIFF(DAY, 0, @starting) = 6 THEN 1 ELSE 0 END -
        CASE WHEN DATEDIFF(DAY, 0, @arrival) = 5 THEN 1 ELSE 0 END
)
END /*FUNCTION*/
4

Thanks for help on these- i think i did hav extraneous parenthesis , i will test building ITVfunction , i may need also to find a way to add holidays to the mix, thanks!