Stored Procedure with relative date parameter

• Howard C. BAchtel-438731

  SSCrazy

  Points: 2384

  More actions

  September 30, 2010 at 11:21 am

  #227479

  I am trying to build a stored procedure using date selection with the date selection being a relative date. The procedure works with the actual date passed, but not with the SQL coded relative date passed.

  Procedure Code below:

  The test code in the following Procedure Code (Declare and Set for Start and End) works fine.:

  --Create Procedure hb_PT_Location_Test @StartDate datetime,@EndDate datetime as

  /* Using input from and to dates, this process identifies all patients in all inpatient locations

  at any time for any duration during the requested time frame */

  /*

  /*

  Declare @StartDate datetime

  Set @StartDate = Dateadd(mm,-4,Getdate())

  Declare @EndDate datetime

  Set @EndDate = Dateadd(mm,-3,Getdate())

  */

  Declare @EndDate1 datetime

  Set @EndDate1 = Dateadd(ss,-1,Dateadd(dd,1,@EndDate))

  Select

  @StartDate as StartDate,

  @EndDate as EndDate

  */

  When run with the following parameters:

  Execute hb_PT_Location_Test @StartDate ='01/01/2010',@EndDate = '01/02/2010'

  The result is:

  2010-01-01 00:00:00.0002010-01-02 00:00:00.000

  When run with the following parameters:

  Execute hb_PT_Location_Test @StartDate = Getdate(),@EndDate = Dateadd(mm,4,Getdate())

  The Result is:

  Incorrect syntax near ')'

  I have tried various different structures of the formats, but can not seem to find one that works.

  Any suggestions are appreciated.

• touchmeknot

  SSCommitted

  Points: 1769

  More actions

  September 30, 2010 at 12:37 pm

  #1229821

  Getdate() can't be passed as parameter

  You may pass it as below

  DECLARE @Date datetime

  SET @Date = getdate()

  exec YourSp @Date

• Howard C. BAchtel-438731

  SSCrazy

  Points: 2384

  More actions

  September 30, 2010 at 1:36 pm

  #1229882

  I suppose that explains why it does not work.

  Thanks for the informaton.:-)