Passing a datePart

How can I pass a datepart to my personal function or stored procedure.
In other words I want to allow a user to pass a datepart such as to compute
by day year, etc... The problem is that when I use the variable that has the
datepart value ie: "d" or "m" or "y" it fails to recognize it. This is a
sample of what I am trying to do:
DECLARE myDatePart varChar(1)
SET myDatePart = "d"
SELECT DATEADD(myDatePart, 1, GETDATE())
I Am trying to create my own datepart function. I do not want to create an
entire if statement to determine what type of datepart is passed into my
procedure.
Thanks in advanceInstead of creating many UDFs or single function with magic numbers as
enums, (if you have 2005) you may want to create a datetime type instead,
that way you can code methods that operate over that type and call like so
(note how intuitive it then becomes):
declare @.sd SqlDate
set @.sd = '1/1/2005'
select @.sd.Day as Day, @.sd.Month as Month, @.sd.Year as Year, @.sd.Hour as
Hour, @.sd.Minute as Minute, @.sd.Second as Second
I have example of this at:
http://channel9.msdn.com/ShowPost.aspx?PostID=147390
William Stacey [MVP]
"Angel" <Angel@.discussions.microsoft.com> wrote in message
news:64F1E3AC-6540-4BDD-99F3-3809DBCE9936@.microsoft.com...
> How can I pass a datepart to my personal function or stored procedure.
> In other words I want to allow a user to pass a datepart such as to
> compute
> by day year, etc... The problem is that when I use the variable that has
> the
> datepart value ie: "d" or "m" or "y" it fails to recognize it. This is a
> sample of what I am trying to do:
> DECLARE myDatePart varChar(1)
> SET myDatePart = "d"
> SELECT DATEADD(myDatePart, 1, GETDATE())
> I Am trying to create my own datepart function. I do not want to create an
> entire if statement to determine what type of datepart is passed into my
> procedure.
> Thanks in advance|||"Angel" <Angel@.discussions.microsoft.com> wrote in message
news:64F1E3AC-6540-4BDD-99F3-3809DBCE9936@.microsoft.com...
> How can I pass a datepart to my personal function or stored
procedure.
> In other words I want to allow a user to pass a datepart such as
to compute
> by day year, etc... The problem is that when I use the variable
that has the
> datepart value ie: "d" or "m" or "y" it fails to recognize it.
This is a
> sample of what I am trying to do:
> DECLARE myDatePart varChar(1)
> SET myDatePart = "d"
> SELECT DATEADD(myDatePart, 1, GETDATE())
> I Am trying to create my own datepart function. I do not want to
create an
> entire if statement to determine what type of datepart is passed
into my
> procedure.
> Thanks in advance
Angel,
I am not sure how your code is running at all.
The above T-SQL returns:
SELECT DATEA.Net SqlClient Data Provider: Msg 155, Level 15, State
2, Line 1
'varChar' is not a recognized CURSOR option.
.Net SqlClient Data Provider: Msg 155, Level 15, State 1, Line 5
'myDatePart' is not a recognized dateadd option.DD(myDatePart, 1,
GETDATE())
For me.
One thing that comes to eye immediately is that there are no "@."
symbols in front of the variable names.
Try:
DECLARE @.myDatePart varChar(1)
SET @.myDatePart = "d"
As for DateAdd, the format is not:
dateadd('d', 1, GetDate())
It is:
dateadd(d, 1, GetDate())
And preferably:
dateadd(d, 1, CURRENT_TIMESTAMP)
Given that DateAdd is looking for an undelimited string literal, I
don't think you can use a variable in this parameter location (but I
don't know that for sure).
Sincerely,
Chris O.|||Security considerations aside, this does the trick:
CREATE PROCEDURE test4 @.dp char(1)
AS
BEGIN
DECLARE @.querystring nvarchar(512)
SET @.querystring = 'SELECT DATEADD(' + @.dp + ', 1, GETDATE())'
EXEC sp_executesql @.querystring
END
EXEC dbo.test4 @.dp='d'
What's strange is using sp_executesql another way does not work. For example
execute sp_executesql
N'SELECT DATEADD(dd, @.daystoadd, GETDATE())',
N'@.daystoadd int',
@.daystoadd = 4
This will work; passing the second parameter @.daystoadd as an integer. But
execute sp_executesql
N'SELECT DATEADD(@.dp, 4, GETDATE())',
N'@.dp varchar(4)',
@.dp = 'dd'
gets the error "Invalid parameter 1 specified for dateadd." Any ideas anyone
?
"Angel" wrote:

> How can I pass a datepart to my personal function or stored procedure.
> In other words I want to allow a user to pass a datepart such as to comput
e
> by day year, etc... The problem is that when I use the variable that has t
he
> datepart value ie: "d" or "m" or "y" it fails to recognize it. This is a
> sample of what I am trying to do:
> DECLARE myDatePart varChar(1)
> SET myDatePart = "d"
> SELECT DATEADD(myDatePart, 1, GETDATE())
> I Am trying to create my own datepart function. I do not want to create an
> entire if statement to determine what type of datepart is passed into my
> procedure.
> Thanks in advance|||You might not have wanted to use IF statements, but CASE works well enough
for a finite set of ten possibilities
CREATE PROCEDURE test6 @.dp char(2), @.count int, @.dateout datetime OUTPUT
AS
BEGIN
IF (@.dp NOT IN ('yy','qq','mm','dy','wk','dd','hh','mi'
,'ss','ms'))
BEGIN
RAISERROR('wrong date format',16,1)
RETURN 1
END
ELSE
BEGIN
SELECT @.dateout =
CASE
WHEN @.dp = 'yy' THEN DATEADD(yy,@.count,GETDATE())
WHEN @.dp = 'qq' THEN DATEADD(qq,@.count,GETDATE())
WHEN @.dp = 'mm' THEN DATEADD(mm,@.count,GETDATE())
WHEN @.dp = 'dy' THEN DATEADD(dy,@.count,GETDATE())
WHEN @.dp = 'wk' THEN DATEADD(wk,@.count,GETDATE())
WHEN @.dp = 'dd' THEN DATEADD(dd,@.count,GETDATE())
WHEN @.dp = 'hh' THEN DATEADD(hh,@.count,GETDATE())
WHEN @.dp = 'mi' THEN DATEADD(mi,@.count,GETDATE())
WHEN @.dp = 'ss' THEN DATEADD(ss,@.count,GETDATE())
WHEN @.dp = 'ms' THEN DATEADD(ms,@.count,GETDATE())
END
END
END
DECLARE @.dateout datetime
EXEC dbo.test6 'yy', 9, @.dateout OUTPUT
SELECT @.dateout
"Angel" wrote:
> That is a good idea but I need to get the result of the DateAdd into a
> variable within the same stored procedure.
> thanks in advance
> "Mark Williams" wrote:
>|||That is a good idea but I need to get the result of the DateAdd into a
variable within the same stored procedure.
thanks in advance
"Mark Williams" wrote:
> Security considerations aside, this does the trick:
> CREATE PROCEDURE test4 @.dp char(1)
> AS
> BEGIN
> DECLARE @.querystring nvarchar(512)
> SET @.querystring = 'SELECT DATEADD(' + @.dp + ', 1, GETDATE())'
> EXEC sp_executesql @.querystring
> END
> EXEC dbo.test4 @.dp='d'
> What's strange is using sp_executesql another way does not work. For examp
le
> execute sp_executesql
> N'SELECT DATEADD(dd, @.daystoadd, GETDATE())',
> N'@.daystoadd int',
> @.daystoadd = 4
> This will work; passing the second parameter @.daystoadd as an integer. But
> execute sp_executesql
> N'SELECT DATEADD(@.dp, 4, GETDATE())',
> N'@.dp varchar(4)',
> @.dp = 'dd'
> gets the error "Invalid parameter 1 specified for dateadd." Any ideas anyo
ne?
> "Angel" wrote:
>