Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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')

share|improve this question
2  
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? –  Aaron Bertrand Nov 27 '12 at 16:20
    
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 :) –  Icono123 Nov 27 '12 at 17:54
    
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. –  Aaron Bertrand Nov 27 '12 at 17:55
    
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. –  Icono123 Nov 27 '12 at 17:58

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
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? –  Icono123 Nov 27 '12 at 18:02
1  
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. –  Aaron Bertrand Nov 27 '12 at 18:04
    
+1 but it would be even better as an inline function rather than a scalar one. –  Paul White Nov 28 '12 at 9:15
    
@SQLKiwi agreed, but I was trying to write a demonstration of something the OP has probably already done, without changing the interface to the function (assuming this was a direct drop-in for Datediff). –  Aaron Bertrand Nov 28 '12 at 12:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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