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