Create Enumerated Function Parameter Type

Question

I want to create a function similar to DATEDIFF in MS SQL Server 2008 R2. The first parameter in DATEDIFF is a datepart. Can I use datepart as a parameter in my function? If not, how do I create a parameter similar to datepart? I don't want to make the parameter text.

For example, I would like to have a function like this: MYFUNC(datepart, timestart, timeend)

So when I call the function, it would look like this: MYFUNC(Hour, N'08:00:00', N'12:00:00')

I'm currently using a nvarchar for the datepart param so my function looks like this: MYFUNC(N'Hour', N'08:00:00', N'12:00:00')

• Aaron Bertrand: No, you can't use a built-in like HOUR as a parameter - you need to pass a value that conforms to one of the data types since your parameter must be declared using a data type. Can you explain what you want to do differently from DATEDIFF?
• Icono123: I'm calculating the duration of someone's sleep based on their start and end sleep times. I'd like to mimic the datepart parameter where I can say give me the duration in hours, minutes, and seconds. Right now I'm using the nvarchar data type to distinquish between hours, minutes, and seconds. The functions works fine, I just want to know how to create the same functionality Microsoft is using for their parameter. It's a lot cleaner than passing a text value and it never hurts to know more :)
• Aaron Bertrand: Right, but why do you need a custom function to do this? You can do this inline with conditionals without requiring a UDF or kludges to make your function seem like datediff.
• Icono123: True, I created a function because I'll have to reuse it multiple places within the database. And I use the same functionality to calculate their nap duration.

Answer 1

You can't do what Microsoft does with built-ins, sorry. You will need to write your function to accept a string, for example, and evaluate it inline, e.g.

CREATE FUNCTION dbo.CustomDateDiff
(
 @datepart VARCHAR(32), -- does not need to be NVARCHAR
 @start DATETIME,
 @end DATETIME
)
RETURNS INT
AS
BEGIN
 RETURN (SELECT CASE @datepart 
 WHEN 'HOUR' THEN DATEDIFF(HOUR, @start, @end) 
 WHEN 'MINUTE' THEN DATEDIFF(MINUTE, @start, @end)
 WHEN 'WEEK' THEN DATEDIFF(WEEK, @start, @end)
 ELSE DATEDIFF(DAY, @start, @end)
 END);
END
GO

Not that you can't use a conditional inside DATEDIFF - a lot of people think you could say:

DATEDIFF(@datepart, 

Or

DATEDIFF(CASE WHEN @datepart = 'HOUR' THEN HOUR END,

But neither of these will work - both yield:

Msg 1023, Level 15, State 1, Line 2
Invalid parameter 1 specified for datediff.

• Icono123: How would you be able to duplicate Microsoft's built-ins? Do you have to use the CLR functionality? Or is it exclusive to Microsoft?
• Aaron Bertrand: You'd have to have access to SQL Server source code and make adjustments there. Any CLR functionality you write still can only accept proper SQL Server data types... never mind that DATEDIFF won't be able to use the variable you pass in anyway.