datepart parameter in system function dateadd
-
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!
-
declare @Datepart varchar(4), @sql varchar(8000)
set @Datepart = 'yyyy'
set @sql = 'SELECT dateadd('+@Datepart+', 2, getdate())'
exec (@sql)
-
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?
-
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.
-
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
-
Thanks for your help anyway.
-
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 -
Does it deserve anything cooler?
_____________
Code for TallyGenerator -
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
-
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
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
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
-
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
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply