Paramaterized SQL C# with DateTimes

Question

I am trying to execute a SQL statement that grabs data within a given time period represented by two dates. Although I have verified that the values of the parameters I am swapping the SQL parameters with are the correct values, I am still getting this exception:

Syntax error converting character string to smalldatetime data type

Here is the C# code:

SqlCommand command = new SqlCommand("SELECT date, @Data FROM datasite WHERE date > '@Start' and date < '@End'", conn);

command.Parameters.AddWithValue("@Data", dataType);

var start = dateRange.StartDate.ToShortDateString();
var end = dateRange.EndDate.ToShortDateString();

command.Parameters.AddWithValue("@Start", start);
command.Parameters.AddWithValue("@End", end);

command.Connection.Open();

SqlDataReader reader = command.ExecuteReader();

while (reader.Read()) { //... }

Pass the DateTime itself? command.Parameters.AddWithValue("@Start", dateRange.StartDate); — Alex K.
– Alex K., Commented May 4, 2016 at 16:33
Passing it as a DateTime results in the same Exception, sorry I should've mentioned that in the post. — treeblah
– treeblah, Commented May 4, 2016 at 16:37
Remove those single quotes when using parameters. '@Start' should just be @Start — LarsTech
– LarsTech, Commented May 4, 2016 at 16:43

Tim Lentine · Accepted Answer · 2016-05-04 16:39:32Z

4

A few things to consider:

You do not need to cast the dates using ToShortDateString(). Instead, you can simply pass the Date variable as the parameter value: command.Parameters.AddWithValue("@Start", dateRange.StartDate);
Your SqlCommand text does not need the single quotes around your @Start and @End variables. Instead, you can use: WHERE date > @Start AND date < @End

answered May 4, 2016 at 16:39

Tim Lentine

7,9225 gold badges38 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

treeblah Over a year ago

Thanks for the clear and concise response. I figured that DateTimes had to be handled like they are when writing raw SQL queries, but these changes fixed the issue.

Scrappy · Accepted Answer · 2016-05-04 16:33:08Z

1

Best to format the date to a known format for the sql statement eg.

   dateRange.EndDate.ToString("yyyy-MM-dd hh:mm:ss");

answered May 4, 2016 at 16:33

Scrappy

1321 silver badge6 bronze badges

1 Comment

treeblah Over a year ago

I tried a few different formats but it still results in the same Exception.

A G · Accepted Answer · 2016-05-04 16:43:41Z

1

You might need to remove the single quotes from the command string.

"SELECT date, @Data FROM datasite WHERE date > '@Start' and date < '@End'"

To

"SELECT date, @Data FROM datasite WHERE date > @Start and date < @End"

answered May 4, 2016 at 16:43

A G

22.9k12 gold badges94 silver badges113 bronze badges

Collectives™ on Stack Overflow

Paramaterized SQL C# with DateTimes

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related