Parameter 1 in DateDiff()

[jalbao]

Declare @schDatePart nvarchar(4)
Set @schDatePart = " "

Select @schDatePart = Sch.sDatePart
FROM tblSchedule Sch
Where Sch.lRecur > 0 AND datediff(@schDatePart,Sch.dtStartTime,getDate()) % Sch.lRecur = 0

In the above query, I am attempting to use the value from the tblSchedule sDatePart field to fill in parameter 1 of the dateDiff() function.

As you can see, I declared a variable named @schDatePart, then I set the variable in the select statement. The Sch.sDatePart values are either d,wk,mm or yy.

When I run the query, SQLServer gives me the following error: Invalid parameter 1 specified for datediff.

I have confirmed that the values in the Sch.sDatePart do not have trialing whitespaces or the like.

I'm thinking that I'm going about this the wrong way - any tips on this?

 

[jalbao]

The following returns the correct results - I just wish there was a more elegant method.

SELECT Sch.lScheduleID,Sch.lRecur,Sch.dtStartTime,getDate() AS currentDate,TF.sDatePart,
(CASE TF.sDatePart
WHEN 'hh' THEN datediff(hh,Sch.dtStartTime,getDate())
WHEN 'dd' THEN datediff(dd,Sch.dtStartTime,getDate())
WHEN 'ww' THEN datediff(ww,Sch.dtStartTime,getDate())
WHEN 'mm' THEN datediff(mm,Sch.dtStartTime,getDate())
WHEN 'yy' THEN datediff(yy,Sch.dtStartTime,getDate())
END) AS DatePartDiff

FROM tblSchedule Sch JOIN tlkpTimeFrame TF ON Sch.lTimeFrameID = TF.lTimeFrameID
WHERE Sch.dtStartTime = getDate()
OR (Sch.lRecur > 0 AND
(CASE TF.sDatePart
WHEN 'hh' THEN datediff(hh,Sch.dtStartTime,getDate())
WHEN 'dd' THEN datediff(dd,Sch.dtStartTime,getDate())
WHEN 'ww' THEN datediff(ww,Sch.dtStartTime,getDate())
WHEN 'mm' THEN datediff(mm,Sch.dtStartTime,getDate())
WHEN 'yy' THEN datediff(yy,Sch.dtStartTime,getDate())
END) % Sch.lRecur = 0
AND getDate() >= Sch.dtStartTime)