2

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

  • 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
2

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.

  • 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

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.