Hi all, I have a query which I want to declare the datepart as part of the datediff variable but I'm getting an error, so i'm guessing I can't use a variable in the datediff function?. Is there another way around this?
Declare @DateDiffPart as VARCHAR(50) = 'day'
Declare @DateDiffVar as INT = 1
where
datediff(DateDiffPart,calldisconnectedtimeutc,getdate()) = @DateDiffVar
hi i guess DYNAMIC SQL would work ..
Declare @DateDiffPart as VARCHAR(50) = 'dd'
Declare @DateDiffVar as INT = 1
declare @SQL varchar(max) = ''
set @SQL =
'select 1 where datediff('+@DateDiffPart+',getdate()-1,getdate()) = '+ CAST(@DateDiffVar AS VARCHAR)
EXEC (@sql)
Use the lowest common denominator type that you would ever need, and for different parts adjust the value accordingly.
For example, if the part you need can go down to hours, use DATEDIFF(HOUR, ... and then multiply the @DateDiffVar by 24 to convert 1 day to HOURs.
SET @DateDiffVar = @DateDiffVar * CASE WHEN @DateDiffPart = 'DAY' THEN 24 ELSE 1 END
If it can go down to minutes, then adjust days by 24 * 60 and hour by * 60.
It would really help if you told us why you need to do this with different types of periods as you requested because it can be done dynamically if you really need it.
