Passing DATEDIFF interval as user defined function argument?

Question

I want to write a function to return datediff ignoring weekends on SQL Server. Is it allowed to pass interval as an argument on my user defined function, as following? SQL Server is saying "Invalid parameter 1 specified for datediff."

CREATE FUNCTION [dbo].[DATEDIFFWD](@interval varchar(2), @ini datetime, @end datetime)
RETURNS int
AS
BEGIN
    DECLARE @output int
    SET @output = DATEDIFF(@interval, @ini, @end)
    IF (@interval = 'hh') SET @output = @output - (DATEDIFF(WK, @ini, @end) * 48)
    IF (@interval = 'dd') SET @output = @output - (DATEDIFF(WK, @ini, @end) * 2)
    RETURN @output
END

user_5070879 · Answer

You could rewrite it as:

CREATE FUNCTION [dbo].[DATEDIFFWD](@interval varchar(2),
                                   @ini datetime, @end datetime)
RETURNS int
AS
BEGIN
    RETURN (SELECT CASE @interval 
      WHEN 'hh' THEN DATEDIFF(hh, @ini, @end) - ((DATEDIFF(WK, @ini, @end) * 48))
      WHEN 'dd' THEN DATEDIFF(dd, @ini, @end) - ((DATEDIFF(WK, @ini, @end) * 2))
    END
    );
END

Please note that scalar function could be inlined starting from SQL Server 2019

db<>fiddle demo

For better performance I would use table function instead:

CREATE FUNCTION [dbo].[DATEDIFFWD](@interval varchar(2),
                                   @ini datetime, @end datetime)
RETURNS TABLE
AS
RETURN (
    SELECT CASE @interval 
     WHEN 'hh' THEN DATEDIFF(hh, @ini, @end) - ((DATEDIFF(WK, @ini, @end) * 48))
     WHEN 'dd' THEN DATEDIFF(dd, @ini, @end) - ((DATEDIFF(WK, @ini, @end) * 2))
    END AS r
);

user_6167855 · Answer

DATEDIFF will not accept user-defined variable equivalents for the DATEPART. You'll have to concatenate this in dynamic SQL or use some other logic to separate that variable outside of DATEDIFF. Also, I wouldn't use varchar(2) on the @interval mainly because it's a bad habit one should kick..

Passing DATEDIFF interval as user defined function argument?

2 Answers