Error converting varchar to date

• EasyBoy

  SSCertifiable

  Points: 5301

  More actions

  January 18, 2017 at 8:14 am

  #334110

  I am receiving error while executing the SP with following parameter.
  I tried using set dateformat but still didn't work.
  SP destinition as below.
     
Create Stored procedure PopulateVolume
@StartDate    date,
    @EndDate    date
AS
BEGIN
    
    /*
    Declare @StartDate date = '2016-01-01'
    Declare @EndDate date = '2016-10-27'

      --*/

      -- interfering with SELECT statements.
    SET NOCOUNT ON;
    
--1) Add all new ECG Volume records to a temp table

      Select        Year(confirmation_date)* 100 + month(confirmation_date) YearMonth,
                region, 
                right (rtrim(Location), 2) as location_id, 
                AccessionId, 
                patient_number, 
                patient_last_name, 
                recorded_by, 
                interpreting_physician_l_name, 
                confirmation_date
    --Into        #tVol
    From        staging.ECGVolume SEV
    Where        not exists (Select 1    from    ECG.ECG_volume EEV
                            Where    right(ltrim(SEV.location),2) = EEV.location_id
                            And        SEV.AccessionId = EEV.AccessionId
                            And        IsNull(SEV.patient_number,'') = Isnull(EEV.patient_number,'')
                            And        SEV.patient_last_name = EEV.patient_last_name
                            And        isnull(SEV.recorded_by,'') = isnull(EEV.recorded_by,'')
                            And        SEV.interpreting_physician_l_name = EEV.physician
                            And        SEV.confirmation_date = EEV.confirmation_date
                            )
    and            confirmation_date between @StartDate and @EndDate

  I am executing SP as below.
  
exec PopulateVolume
        @StartDate = 'dateadd(D,-15,getdate())',
        @EndDate = 'getdate()'


  I am getting the error error
  Msg 8114, Level 16, State 5, Procedure spPopulateECGRecall, Line 25
  Error converting data type varchar to date.

  Can anyone please help?

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  January 18, 2017 at 8:22 am

  #1923441

  You can't do calculations on the fly like that.  Try this:
  
DECLARE
    @StartDate date
,   @EndDate date;
SELECT
   @StartDate = DATEADD(day,-15,GETDATE())
,   @EndDate = GETDATE();
exec PopulateVolume
   @StartDate = @StartDate,
   @EndDate = @EndDate;


• EasyBoy

  SSCertifiable

  Points: 5301

  More actions

  January 18, 2017 at 8:22 am

  #1923442

  Anyhow, i found the solution.

  DECLARE @sdate DATETIME
  SET @sdate = dateadd(D,-15,getdate())
  DECLARE @edate DATETIME
  SET @edate = getdate()

  exec sp... @sdate, @edate

  Thanks all.
  <b

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  January 18, 2017 at 8:23 am

  #1923444

  You're assigning string values to dates. You need to define the variables to assign values and then use the variables to send their values as parameters to the procedure.
  
Declare @StartDate date = dateadd(D,-15,getdate()),
   @EndDate date = getdate();

  exec PopulateVolume @StartDate, @EndDate;


  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2