VB.NET, SQL Server and list of dates

I have a query that takes as a parameter a list of dates. In VB.NET the dates are in a string ArrayList and I am using the String.Join() method to get them in a comma delimited list. The problem is when I do that double quotes are put at the start and end of the string and SQL complains about that (I think; see below). How can I get a list of date from a string ArrayList without the quote.

My arraylist contains these values:

'2020-08-30'
'2020-08-27'
'2020-09-28'
'2020-09-09'
'2020-08-31'
'2020-08-29'

when I join them using String.Join(",", sDates) I get the following:

"'2020-08-30','2020-08-27','2020-09-28','2020-09-09','2020-08-31','2020-08-29'"

and when I use that in a parameter query it gets rejected. comm.Parameters.AddWithValue("@dates", String.Join(",", sDates))

sql contains the following"

...where pj.ProjectName =@projectname And tcd.Date in (@dates)

Exact error I get is

    System.Data.SqlClient.SqlException
    HResult=0x80131904
    Message=Incorrect syntax near ','.
    Source=.Net SqlClient Data Provider

Any advice?

This error message:

Incorrect syntax near ','

Is not caused by the way you've done your IN. It is caused by something else, such as a misplaced comma in a select block:

SELECT , a FROM x
       ^

The way you've done your IN won't work either, because it's conceptually the same as writing this:

SELECT * FROM table WHERE dateColumn IN ('''2000-01-01'',''2000-02-01''')

There is no such date with a string value of '2000-01-01','2000-02-01'.

If you want to use IN in the style you're attempting here, you have to add a parameter per date value and set it up accordingly:

sqlCommand.CommandText = "SELECT * FROM table WHERE dateCol IN("
Dim p = 0
For Each d as DateTime in MyDateList
  sqlCommand.CommandText &= "@p" & i & "," 'concat param placeholder on
  sqlCommand.Parameters.AddWithValue("@p" & i, d)
  i += 1
Next d

sqlCommand.CommandText = sqlCommand.CommandText.TrimEnd(","c) & ")" 'get rid of trailing comma and close the IN brackets

This will generate an sql like

SELECT * FROM table WHERE dateCol IN (@p0,@p1,@p2)

with 3 parameters, and a populated parameters collection. You've already been pointed to Joel's blog about AddWithValue, so I won't repeat it.. But i did want to say that the way you've presented your question implies you have a list of strings, not datetimes. You should definitely make sure your list has DateTimes in, and your db column should be a date based type, not a string based type