Pass Interval parameter for function in SQL

Question

I want to create a function that returns a temporary date table for generating a report. I want to specify a dynamic spacing for a table. Below is a function that encapsulates my problem.

Consider:

CREATE FUNCTION [dbo].[DateAdder] (@DateStart datetime, @increment int, @interval ?????) Returns datetime AS BEGIN Declare @Return datetime SELECT @Return = DATEADD(@interval, @increment, @DateStart) return @Return END 

Is there a way to pass the Interval to this function?

(Obviously, I'm not trying to rewrite the DATEADD function, I just use this as an example to highlight my problem).

Hooray!

Answer 1

You cannot pass a parameter as an interval parameter for DATExxx functions. The easiest way would be to pass varchar and then duplicate the DATExxx function inside the CASE statement. For instance:.

 SELECT @Return = CASE @interval WHEN 'month' THEN DATEADD(month, @increment, @DateStart) WHEN 'week' THEN DATEADD(week, @increment, @DateStart) WHEN 'day' THEN DATEADD(day, @increment, @DateStart) END 

Personal preferences - I never remember cute cuts, I always set out the intervals in full.