Can I use the same keywords/symbols as DATEADD and DATEPART in user-defined functions

Question

The SQL functions DATEADD, DATEDIFF and DATEPART take a keyword parameter specifying the date/time period you're dealing with - it's not a varchar/string, it's a bare keyword. (https://learn.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql)

I'm creating some user-defined functions that do date/time manipulation, and need to include an argument which does the same thing as these functions - i.e. I want to create a user defined function that looks like this:

DATESEQUENCE(DAY, '2017-01-01', '2017-06-01') 

or

DATESEQUENCE(MINUTE, '2017-07-31 11:00', '2017-07-31 15:00')

Is there any way to define an argument to a user-defined function using the built-in set of keywords/symbols for date/time periods?

Answer 1

Although you can't pass a datepart keyword as a parameter to a user-defined function, you can pass a string literal and perform the needed conversion. Below is an table-valued function example that leverages a numbers table to return an adhoc range of datetime values you can extend as needed.

CREATE TABLE dbo.Numbers(
    Number int NOT NULL
        CONSTRAINT PK_Numbers PRIMARY KEY
    );
WITH 
     t10 AS (SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t(n))
    ,t1000 AS (SELECT 0 AS n FROM t10 AS a CROSS JOIN t10 AS b CROSS JOIN t10 AS c)
    ,t1000000 AS (SELECT ROW_NUMBER() 
        OVER (ORDER BY (SELECT 0)) AS num FROM t1000 AS a CROSS JOIN t10 AS b CROSS JOIN t10 AS c CROSS JOIN t10 AS d)
INSERT INTO dbo.Numbers WITH (TABLOCKX)
SELECT num - 1 AS Number
FROM t1000000;
GO

CREATE FUNCTION dbo.DATESEQUENCE(
      @DatePartName varchar(7)
    , @StartValue datetime2(0)
    , @EndValue datetime2(0)
    )
RETURNS TABLE AS
RETURN(
    WITH UnitOfMeasure AS (
        SELECT CASE @DatePartName
            WHEN 'second' THEN 1
            WHEN 'minute' THEN 60
            WHEN 'hour' THEN 3600
            WHEN 'day' THEN 86400
            WHEN 'month' THEN 1
            WHEN 'year' THEN 12
        END Units
    )
    SELECT 
        CASE WHEN @DatePartName IN('month', 'year') THEN 
            --use month for month and year
            DATEADD(month, Number * Units, @StartValue)
        ELSE
            --use second for second, minute, hour, and day
            DATEADD(second, Number * Units, @StartValue) END AS DateSequence
    FROM dbo.Numbers
    CROSS JOIN UnitOfMeasure
    WHERE
        Number <= CASE WHEN @DatePartName IN('month', 'year') THEN 
            DATEDIFF(month, @StartValue, @EndValue) / Units
        ELSE
            DATEDIFF(second, @StartValue, @EndValue) / Units
        END
);
GO