locked
Adding integer value to datetime in SQL RRS feed

  • Question

  • Hi,

    I am trying to add integer value to the datetime to get the configurable "date".

    But it is not working out. Here is the code for it.

    declare @Id int;
    exec @Id = up_GetConfigurableDayInterval
    print getdate() + @Id

    The sp "up_GetConfigurableDayInterval", will return number of days to add the the current date time. 

    But print getdate() + @Id , is not producing the updated result.

    But when ever i have replaced the variable "@Id" with a value, Say "5", it is producing the expected result.

    I have also tested the above code by the following:-

    create table Temp(value datetime);
    insert into Temp values(getdate() + @Id);
    select * from Temp

    But it is not having the new added datetime.

    May i know, how to get it.

    Thanks in advance.


    NANDAKUMAR.T

    Tuesday, August 4, 2015 10:23 AM

Answers

  • So it's clear that @id=0 that's why the result is still Getdate().

    There is a SELECT statement in your stored procedure "GetConfigurableDayInterval" which returns 7.

    Try to convert your sp to a function since it returns an INT.


    Please click "Mark As Answer" if my post helped.

    • Marked as answer by NANDAKUMAR T Tuesday, August 4, 2015 12:54 PM
    Tuesday, August 4, 2015 11:19 AM

All replies

  • Try dateadd function as follows:

    declare @Id int; exec @Id = up_GetConfigurableDayInterval --print getdate() + @Id

    print dateadd(d,@Id,getdate())



    Please click "Mark As Answer" if my post helped.

    Tuesday, August 4, 2015 10:27 AM
  • Hi,

    I have tried your answer.

    But it still prints the current date and not the updated date.

    This is the code, i have tried.

    select DATEADD(day,@Id,getdate())

    Thanks.


    NANDAKUMAR.T

    Tuesday, August 4, 2015 10:44 AM
  • declare @Id int;
    exec @Id = up_GetConfigurableDayInterval
    print getdate() + @Id

    With this EXEC call you assign the return code of the SP to the variable, and by default this is 0; Current date - 0 is still Current Date.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, August 4, 2015 10:50 AM
  • Hi,

    I got the value, is updated in the @Id variable.

    But after adding, it is not giving the updated result.

    May i know why.

    Thanks.



    NANDAKUMAR.T

    Tuesday, August 4, 2015 10:59 AM
  • What is the value of @Id?

    Please click "Mark As Answer" if my post helped.

    Tuesday, August 4, 2015 11:09 AM
  • Hi,

    I think i have missed something.

    This is my code.

    declare @Id int;
    exec @Id = up_GetConfigurableDayInterval
    select 1
    select @Id 

    which produces the result as

    7

    1

    0

    I think the value is not get persisted in the "@Id" variable.

    May i know how to solve it.

    Thanks.


    NANDAKUMAR.T

    Tuesday, August 4, 2015 11:14 AM
  • create table #Temp(value datetime);

    DECLARE @Id INT =2
    insert into #Temp values(getdate() + @Id);--- add two days 
    select * from #Temp

    value
    2015-08-06 14:16:00.210

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, August 4, 2015 11:16 AM
    Answerer
  • So it's clear that @id=0 that's why the result is still Getdate().

    There is a SELECT statement in your stored procedure "GetConfigurableDayInterval" which returns 7.

    Try to convert your sp to a function since it returns an INT.


    Please click "Mark As Answer" if my post helped.

    • Marked as answer by NANDAKUMAR T Tuesday, August 4, 2015 12:54 PM
    Tuesday, August 4, 2015 11:19 AM
  • Hi,

    Thanks. It worked.

    But may i know, why i can not reproduce the same in the SP. 

    Thanks once again.


    NANDAKUMAR.T

    Tuesday, August 4, 2015 12:55 PM
  • Create a function 'udfGetConfigurableDayInterval' that returns INT and then :

    SET @Id=udfGetConfigurableDayInterval


    Please click "Mark As Answer" if my post helped.

    Tuesday, August 4, 2015 1:12 PM
  • HI,

    I have asked for the SP and not for the fn.

    Thanks.


    NANDAKUMAR.T

    Tuesday, August 4, 2015 1:15 PM
  • So create a SP with OUTPUT parameter.

    https://msdn.microsoft.com/en-us/library/ms188655.aspx?f=255&MSPPError=-2147217396


    Please click "Mark As Answer" if my post helped.

    • Proposed as answer by Naomi N Tuesday, August 4, 2015 3:01 PM
    Tuesday, August 4, 2015 1:20 PM