DATEPART questions

Hello,

I need to use the statement DATEPART(datepart, datetime) , but i want to set dinamically the datepart parameter. Something like this:

CREATE PROCEDURE my_procedure (@.datepart_parameter ...)
AS
BEGIN
...
DATEPART (@.datepart_parameter, my_table.datetime)
....
END
GO

But it doesn't work. Someone knows how I can do this?

Thank you very much for your help.

Jonathan

declare @.var varchar(5)

declare @.sql varchar(50)

set @.var= 'day'

set @.sql = '(select datepart('+@.var+',getdate()))'

exec(@.sql)

Pls read this doc....as with dynamic sql u gotta be careful.. http://www.sommarskog.se/dynamic_sql.html

|||thank you nitin. There is an other way to do this, without dinamyc sql,please?|||

hi,

ur already passing the datepart as a dynamic parameter...try to pass to the proc the value of that datepart as well beforehand...so that u can use it..say pass it ..month and 10 ...

or write a case statement within the proc...

declare @.datepart varchar(5)

set @.datepart='day'

select case @.datepart

WHEN 'day' THEN datepart(day,getdate())

WHEN 'month' THEN datepart(month,getdate())

ELSE 'Unknown'

END

|||

Here's a custom function to do this for all the datepart names, it will return NULL if you use an invalid name.

CREATE FUNCTION DynamicDatePart(@.datepart varchar(11), @.date datetime)
RETURNS int
AS
BEGIN
DECLARE @.retVal int
SET @.retVal = CASE
WHEN @.datepart in ('year', 'yy', 'yyyy') THEN YEAR(@.date)
WHEN @.datepart in ('quarter', 'qq', 'q') THEN DATEPART(quarter, @.date)
WHEN @.datepart in ('month', 'mm', 'm') THEN MONTH(@.date)
WHEN @.datepart in ('dayofyear', 'dy', 'y') THEN DATEPART(dayofyear, @.date)
WHEN @.datepart in ('day', 'dd', 'd') THEN DAY(@.date)
WHEN @.datepart in ('week', 'wk', 'ww') THEN DATEPART(week, @.date)
WHEN @.datepart in ('weekday', 'dw') THEN DATEPART(weekday, @.date)
WHEN @.datepart in ('hour', 'hh') THEN DATEPART(hour, @.date)
WHEN @.datepart in ('minute', 'mi', 'n') THEN DATEPART(minute, @.date)
WHEN @.datepart in ('second', 'ss', 's') THEN DATEPART(second, @.date)
WHEN @.datepart in ('millisecond', 'ms') THEN DATEPART(millisecond, @.date)
END
RETURN @.retVal
END

Call it, like this

DECLARE @.dp varchar(20)
SET @.dp = 'yy'
SELECT dbo.DynamicDatePart(@.dp, getdate())

|||Use a CASE expression in your code to evalute one of the DATEPART expressions based on parameter. This is the most efficient way to do it.|||Thank you everybody for your time, and you answers.

Have a good day.