3

I am trying to send a comma delimited string of dates to my stored procedure, to be used in an IN clause. However, I am getting the error "Conversion failed when converting date and/or time from character string."

I am trying to use these dates to find prices that match. C#:

 StringBuilder LastDaysOfEveryMonth = new StringBuilder();
 DataAccess da = new DataAccess();
 SqlCommand cm = new SqlCommand();
 cm.CommandType = CommandType.StoredProcedure;
 var pList = new SqlParameter("@DateList", DbType.String);
 pList.Value = LastDaysOfEveryMonth.ToString();
 cm.Parameters.Add(pList);
...
 cm.CommandText = "spCalculateRollingAverage";
 DataSet ds = da.ExecuteDataSet(ref cm);

When debugging it, the value of the passed string is :

'2013-07-31','2013-08-30','2013-09-30','2013-10-31','2013-11-29','2013-12-31',
'2014-01-31','2014-02-28','2014-03-31','2014-04-03',

with the DbType String and SQLDbType NvarChar.

Any advice would be much appreciated! SQL:

CREATE PROCEDURE [dbo].[spCalculateRollingAverage] 
@StartDate DateTime,
@EndDate DateTime,
@Commodity nvarchar(10),
@PeakType nvarchar (10),
@BaseID int,
@NumberOfMonths int,
@DateList nvarchar(MAX)  

AS

    BEGIN

    select TermDescription,ContractDate,Price,SortOrder into #tbtp from BaseTermPrice 
    inner hash join Term 
    on 
    Term.TermID = BaseTermPrice.TermID
    where 
    BaseID = @BaseID and ((@PeakType IS NULL and PeakType is null) or 
    (@PeakType IS     NOT NULL and PeakType=@PeakType))
    and ((@DateList IS NULL and ContractDate between @StartDate and @EndDate) 
     or (@StartDate IS NULL and ContractDate in (@DateList)))
    order by
    ContractDate,SortOrder
Pragnesh Khalas
  • 2,908
  • 2
  • 13
  • 26
user3565588
  • 33
  • 3

1 Answers1

2

You cannot use a varchar variable in an IN clause like this. You have to either add it to a dynamic SQL to execute, or - split it into temp table/temp variable.

For example using this SplitString function you can do something like this:

or (@StartDate IS NULL and ContractDate in 
          (SELECT Name from dbo.SplitString(@DateList))))
Community
  • 1
  • 1
Yuriy Galanter
  • 38,833
  • 15
  • 69
  • 136