May 23, 2007 at 1:36 pm
The normal systax for dateadd is:
SELECT dateadd(yyyy, 2, getdate())
I would like to call the function with my own datepart. The follow does not work:
declare @Datepart varchar(4)
set @Datepart = 'yyyy'
SELECT dateadd(@Datepart, 2, getdate())
Any ideas? Thanks!
May 23, 2007 at 1:39 pm
declare @Datepart varchar(4), @sql varchar(8000)
set @Datepart = 'yyyy'
set @sql = 'SELECT dateadd('+@Datepart+', 2, getdate())'
exec (@sql)
May 23, 2007 at 1:48 pm
Thanks. That was fast! I was hoping to avoid dynamic sql, but seeing how short it is I see that it's probably a better solution than a lot of if statements.
What does one call the quoteless varchar datatype?
May 23, 2007 at 1:56 pm
May 23, 2007 at 2:15 pm
Normally one would set a varchar variable with single quotes.
I just realized that the dynamic sql won't work because I need to set a variable to the date returned by dateadd. sp_executesql allows output parameters but is more complicated.
May 23, 2007 at 3:13 pm
I think that means you have to set up a bunch of if statements that executes the right datediff statement based on the parameter
declare @Datepart varchar(4), @sql nvarchar(100),@res datetime
set @Datepart = 'yyyy'
if @datepart='yyyy'
set @res = (SELECT dateadd(yyyy, 2, getdate()))
if @datepart = 'mm'
set @res = (SELECT dateadd(mm, 2, getdate()))
select @res
May 23, 2007 at 3:37 pm
Thanks for your help anyway.
May 23, 2007 at 3:40 pm
May 23, 2007 at 6:12 pm
You can easily make a UDF with 3 parameters from it and use it in your scripts instead of standard DATEADD:
SET @DatePart = 'yyyy' SET @AddedParts = 2 SET @AddedDate = GetDATE()
SELECT dbo.MyDateAdd(@DatePart, @AddedParts, @AddedDate)
_____________
Code for TallyGenerator
May 23, 2007 at 7:21 pm
May 23, 2007 at 8:08 pm
Does it deserve anything cooler?
_____________
Code for TallyGenerator
May 23, 2007 at 9:54 pm
What's so complicated about sp_executesql?
DECLARE @DatePart VARCHAR(4), @DynamicSQL NVARCHAR(4000), @ReturnDate DATETIME
SET @DatePart = 'yyyy'
SET @DynamicSQL = 'SET @ReturnDate = DATEADD(' + @DatePart + ', 2, GETDATE())'
EXECUTE sp_executesql @DynamicSQL, N'@ReturnDate DATETIME OUTPUT', @ReturnDate OUTPUT
May 23, 2007 at 10:53 pm
Nicely done, Ed...
What I'd really like to know, though, in the original post, Janet said "I would like to call the function with my own datepart."...
My questions would be... why? What are you using this for? Can you post some example code of what you intend to use this for so we can make sure we're giving you the best answer?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 24, 2007 at 8:50 am
Yes, Ed, nicely done. I didn't realize that you could grab the return value like that. I'm writing some business date functions and would like to be able to pass dateparts easily.
And yes, Sergiy. Cool is good! Makes me smile.
Thanks, all. I think we have exhausted this one. Since I had such great response, I'll try again with my next question. Stay tuned, JP
May 24, 2007 at 9:12 am
You still haven't told us, Janet... why do you need to do such a thing? Might lead to something even "cooler" if we knew...
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply