none
What cant i pass a parameter to DATEADD

    Question

  • DECLARE @THISDATE AS DATETIME
    SET @THISDATE = DATEADD("D", -14, GETDATE())
    SELECT F.PROP, F.NUMBER, F.NUM, C.FN, C.LN, C.PHONE, E.EMAIL, F.ARRIVAL, F.DEPARTURE, F.STATUS
    FROM ADDRESS E WITH (NOLOCK)
    LEFT JOIN CLIENTEL C ON C.PROP = E.PROP AND E.IDNUMBER = C.CLIENTNUMBER
    INNER JOIN FOLIOLINK F ON F.PROPID = C.PROPID AND F.CLIENTNUMBER = C.CLIENTNUMBER
    WHERE E.EMAIL LIKE '%@%GOV%' AND F.ARRIVAL <= getdate() AND F.DEPARTURE >= @thisdate
    AND F.FOLIONUMBER IN (SELECT DISTINCT R.FOLIONUMBER FROM RESERVATIONRATE R WHERE PROPID = E.PROPID AND RATECHOICE IN (SELECT SEASONNUMBER FROM SEASONS WHERE PROPID = E.PROPID AND SHORTNAME LIKE '%GOV%'))
    order by F.PROPID, C.FIRSTNAME

    In the above syntax I am getting a error while parsing the error is Error 1023: Invalid Parameter 1 specified for dateadd.

    Monday, July 29, 2013 9:40 PM

Answers

All replies

  • Try to use DAY instead of "D"

    • Edited by dsmwb Monday, July 29, 2013 9:51 PM typo
    • Proposed as answer by dsmwb Monday, July 29, 2013 10:00 PM
    Monday, July 29, 2013 9:49 PM
  • In Transact-SQL the first parameter for the DateDiff function is a constant value such as Day or DD for Day parameter. 

    Check DateDiff function in BOL for details.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    Monday, July 29, 2013 10:03 PM
    Moderator
  • Hi,

    Thanks for your quick reply, I tried but it was not working, same error came.

    --

    Kishore

    Monday, July 29, 2013 10:31 PM
  • Hi,

    Thanks for your quick reply, I tried but it was not working, same error came.

    --

    Kishore

    Monday, July 29, 2013 10:32 PM
  • In my SQL Server 2008 R2 it works both with "D" and with DAY

    DECLARE @THISDATE AS DATETIME
    SET @THISDATE = DATEADD("D", -14, GETDATE())
    PRINT @THISDATE

    result: 16 Jul 2013 12:55AM

    DECLARE @THISDATE AS DATETIME
    SET @THISDATE = DATEADD(DAY, -14, GETDATE())
    PRINT @THISDATE
    result: 16 Jul 2013 12:55AM



    • Edited by dsmwb Monday, July 29, 2013 11:07 PM typo
    Monday, July 29, 2013 10:57 PM
  • Where exactly you're executing this code and what is your SQL Server version?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, July 29, 2013 11:08 PM
    Moderator
  • DECLARE @THISDATE AS DATETIME
    SET @THISDATE = DATEADD("D", -14, GETDATE())

    Don't enclose the datepart parameter in quotes.  Try:

    DECLARE @THISDATE AS DATETIME;
    SET @THISDATE = DATEADD(d, -14, GETDATE());

    See http://msdn.microsoft.com/en-us/library/ms186819.aspx for syntax and examples.

     

     


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Tuesday, July 30, 2013 1:10 AM
  • This works in SQL 2012 also,

    DECLARE @THISDATE AS DATETIME
    SET @THISDATE = DATEADD("D", -14, GETDATE())
    select @THISDATE
    SET @THISDATE = DATEADD([D], -14, GETDATE())
    select @THISDATE

    Let us know the version you are using? 

    Also did you try executing the first two lines of the code posted separately?


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, July 30, 2013 2:39 AM