1

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://docs.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?

  • You might want to post the code of your function. If you do not have it completed, post what you have so far. – SqlWorldWide Jul 31 '17 at 11:39
  • 1
    I think it's impossible: the built-in functions are written in C# and those day, week, month, ecc are constants available in those functions only – sepupic Jul 31 '17 at 12:50
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.