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:


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


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

    Message=Incorrect syntax near ','.
    Source=.Net SqlClient Data Provider

Any advice?

asked on Stack Overflow Oct 5, 2020 by barjan99 • edited Oct 5, 2020 by Dale K

1 Answer


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:


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

answered on Stack Overflow Oct 7, 2020 by Caius Jard • edited Oct 7, 2020 by Caius Jard

User contributions licensed under CC BY-SA 3.0