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?

  • not sure what you mean by 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.

  • Don't lose hope.  There are a bunch of more knowledgable people than me on in the evenings.  Jeff Moden or Sergiy may have a better answer for you.


  • 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

  • geez, I expected something cooler


  • 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)

  • 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)

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply