Dynamic DatPart in datediff function

[Bobbber]

Hi All...

I'm trying to calculate a dynamic period by using a value from a table to determine the period used. i.e. in some cases I'd like to compare the dates in days and other cases in years, months etc...

DECLARE @DatePart varchar(50)
SELECT @DatePart = [DatePart] FROM tblDateParts

SELECT DateDiff (@DatePart, '2009-01-01', '2010-01-01)

In the bottom statement it fails with a "Invalid parameter 1 specified for datediff". This is because the first parameter needs to be 'wk' keyword as opposed to "wk" in a string?

I'm confused as to why the first DatePart parameter behaves this way (kinda reduces flexability I'd have thought) - does anyone know a way around this?

Many Thanks!

Rob

 

[markros]

The only way would be to use CASE statement logic,
e.g

CASE WHEN @DatePart = 'Day' then datediff(Day,@FirstDate, @SecondDate) case when @DatePart = 'Week' then ... etc. end


PluralSight Learning Library

 

[gmmastros]

I would be tempted to use a case statement here.

ex:

Select case @DatePart
            When 'wk' then DateDiff(wk, Date1, Date2)
            When 'y'  then DateDiff(year, Date1, Date2)
            End As AliasName

Alternatively, you could write your own datediff function that hides the implementation details, like this.

Create Function dbo.DynamicDateDiff
  (@Interval VarChar(2),
   @Date1 DateTime,
   @Date2 DateTime)
Returns Int
As 
Begin
  Return (Select Case @Interval 
                   When 'wk' Then DateDiff(wk, @Date1, @Date2)
                   When 'y' Then DateDiff(year, @Date1, @Date2)
                   End
         )
End

Then you can call it like this:

Select dbo.DynamicDateDiff(DatePart, Date1, Date2) As Interval
From   tblDateParts

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom