c# - ERROR: Failed to convert parameter value from a String to a DateTime -

i have faced error: failed convert parameter value string datetime when trying pass multiple selected dates checkboxlist parameter used in sql.

i have tried other datatypes such nvarchar , works when pass multiple selected values 1 stored procedure parameter , return select statement using dynamic sql populate gridview.

ps. in webserver i'm displaying in checkboxlist e.g 31-aug-2013, using date.datatextformatstring = "{0:dd-mmm-yyyy}";. in sql database, displayed e.g 2013-08-31.

aspx.cs

    protected void page_load(object sender, eventargs e)     {         date.datatextformatstring = "{0:dd-mmm-yyyy}";          using (sqlconnection conn = new sqlconnection(dbconn))         {             try //call stored procedure             {                  sqlcommand cmd = new sqlcommand(spddl, conn);                 sqldataadapter da = new sqldataadapter(cmd);                 dataset ds = new dataset();                 da.fill(ds);                 if (!ispostback)                 {                     date.datasource = ds.tables[0];                     date.datatextfield = ds.tables[0].columns["date"].tostring();                     date.databind();                    }                 if (ispostback)                 {                     bind();                 }                  }              catch (exception i)             {                 bool exception = true;                 if (exception == true)                 {                     //txtmessage.text += e.message;                 }             }         }     }  public void bind()    {            using (sqlconnection conn = new sqlconnection(dbconn))        {            using (sqlcommand cmd = new sqlcommand(spretrieve, conn))            {                  string selecteddate = string.empty;                   if (date.selectedvalue == "all")                 {                     selecteddate = "date";                 }                 else                 {                     foreach (listitem item in date.items)                     {                         if (item.selected)                         {                             selecteddate += "'" + item.text + "',";                         }                     }                      selecteddate = selecteddate.substring(0, selecteddate.length - 1);                 }                  cmd.commandtype = commandtype.storedprocedure; cmd.parameters.add("@param", sqldbtype.datetime).value = selecteddate;                  conn.open();                sqldataadapter da = new sqldataadapter(cmd);                dataset ds= new dataset();                da.fill(ds);                gridview.datasource = ds.tables[0];                gridview.databind();             }        } 

sql

alter procedure [dbo].[sp]  @param nvarchar(512)  begin -- set nocount on added prevent result sets -- interfering select statements. set nocount on; declare @sql nvarchar(max) set @sql = 'select * tablename [column] in (' + @param + ')' exec sp_executesql @sql; end 

on sql side..
if [column] datetime type should convert first

convert(varchar, [column], 112)

i.e. sp should like:

alter procedure [dbo].[sp]  @param nvarchar(512)  begin -- set nocount on added prevent result sets -- interfering select statements. set nocount on; declare @sql nvarchar(max) set @sql = 'select * tablename convert(varchar, [column], 112) in (' + @param + ')' exec sp_executesql @sql; end 

on code side..
thing parameter, if passing multiple dates in @param have convert them before passing :

foreach (listitem item in date.items) {     if (item.selected)     {         datetime dttemp = convert.todatetime(item.value);         selecteddate += "'" + dttemp.tostring("yyyymmdd") + "',";     } }  selecteddate = selecteddate.substring(0, selecteddate.length - 1);