CODEWITHSUNDEEP
c#sqlsql-serverasp.net-web-api2
treeblah
treeblah

Reputation: 1265

Paramaterized SQL C# with DateTimes

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()) { //... }

Upvotes: 0

Views: 1090

Answers (3)

A G
A G

Reputation: 22597

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"

Upvotes: 1

Tim Lentine
Tim Lentine

Reputation: 7862

A few things to consider:

  1. 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);
  2. 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

Upvotes: 4

Scrappy
Scrappy

Reputation: 132

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

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

Upvotes: 1

Related Questions