Datediff and Variables
-
Simple Question can you assign a variable to the datepart portion of a datediff statement? If so how?
I would like to create a report with grouped fields by week, year, or quarter so far I have this
declare @partition char (6)
, @enddate varchar (15)
, @NoOfPar int
set @partition = 'wk'
set @enddate = '5/21/2004'
set @noofpar = 2
select p.BlockID
,p.Reason_for_Scrap
,p.LBS
,b.ID#
,b.PrintDate
,datediff(@partition ,printdate,@enddate) as part
from dbo.PrintScrap p
join dbo.blocks b
on p.blockID = b.blockID
when I try to run this in QA I get an error
“Invalid parameter 1 specified for datediff.”
If I replace the variable with a standard partition like ‘wk’ it works fine. I would like to avoid dynamic SQL if possible but if not so be it.
Cory McRae
-
Dynamic SQL is the only working way. Try this, although no SQL statistics can be calculated on it:
declare @partition varchar (6)
, @enddate varchar (15)
, @NoOfPar int
set quoted_identifier off
set @partition = 'wk'
set @enddate = '5/21/2004'
set @noofpar = 2
declare @sql varchar(60)
set @sql = "select Datediff(" + @partition + ", printdate, '" + @enddate + "') from PrintScrap "
select @sql
exec(@sql)
I left out some fields and the join. Not sure of the data type of datepart first parameter but is not a character, which is what you are passing.
-
Why not use a case statment to get what you want
ie
select blah blah,
case @partition
when 'wk'
then datediff(wk,printdate,@enddate)
when 'mm'
then datediff(mm,printdate,@enddate)
when 'hh'
then datediff(hh,printdate,@enddate)
end
as part
-
The Case statement worked great thank you for your input.
Cory
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply