DATEPART as a parameter

Question

I'm guessing this is not possible since the engine doesn't like it, but is there a way (barring dynamic SQL) to pass in a DATEPART as a parameter to a procedure?

Answer 1

Why can't you just pass in the Date that is created from the Datepart?

DECLARE @datepart DATETIME
SET @datepart = DATEPART(yyyy, GetDate())

exec spName @datepart

Answer 2

No. There is no "year" datatype. You can do this:

CREATE PROC myproc @Year int, @Month tinyint

This fails because DATEPART part is a literal

DECLARE @part varchar(10)
SET @part = 'month'
SELECT DATEPART(@part, GETDATE());

or this and an IF ELSE

CREATE PROC myproc @WhoeDate datetime, @part varchar(10) /* year. month etc*/

Answer 3

One option is to use a CASE with the parameter (it would be better to use an int enumeration instead of string):

declare @part varchar(10)
set @part = 'YEAR'
select case @part 
            when 'YEAR' then datepart(yy, SomeDateTimeColumn) 
            when 'MONTH' then datepart(mm, SomeDateTimeColumn) 
            else datepart(dd, SomeDateTimeColumn) 
        end
from SomeTable

Answer 4

If you are trying to create a dynamic stored procedure the following code could you help you to achieve this.

When you define the body of the sp, remember that you need to use exec sql key-word to execute the sql sentence:

create proc sp_dinamic_procedure(@datepart_sql varchar(20), @datepart_var varchar(8) )
as
begin   
    declare @query varchar(100) 
    select @query = "select date from table1 t1 where datepart(" + @datepart_var + ", t1.fecha1) = " + @datepart_sql
    exec @query
end

Then you can pass any sql sentence to your stored procedure as a single string dependig on your needs:

declare @datepart_sql varchar(20), @datepart_var varchar(8)
select @datepart_sql = "datepart(year,getdate()", @datepart_var = "year"
exec sp_dinamic_procedure @datepart_sql, @datepart_var