CODEWITHSUNDEEP
c#sql-serverdatetime
usertfwr
usertfwr

Reputation: 309

SQL Request with Timestamp

I'm using C# to do a request to a SQL server. The request need to be between 2 Timestamps (date and hour). The problem is if I put date only (2015-04-15) it works but if I put time behind (2015-04-15 16:00:00) it doesn't work anymore and show the error : "Close to '16' the syntax is incorrect."

I try different things but I can't find the way.

Here is my code:

DateTime Endtime = Convert.ToDateTime(DateTime.Now.Date.ToString("d") + " " + DateTime.Now.AddHours(1).Hour.ToString("00") + ":00:00");
DateTime Starttime = Convert.ToDateTime(DateTime.Now.Date.ToString("d") + " " + DateTime.Now.Hour.ToString("00") + ":01:00");

string time = string.Empty;

SqlConnection sqlCon = new SqlConnection("...");
sqlCon.Open();
SqlCommand sqlCmd = new SqlCommand("SELECT COUNT(TimeStamp) FROM net WHERE Timestamp BETWEEN " + Starttime.ToString("yyyy-MM-dd hh:mm:ss") + " AND " + Endtime.ToString("yyyy-MM-dd hh:mm:ss"), sqlCon);

SqlDataReader reader = sqlCmd.ExecuteReader(); //Error comes from here
while (reader.Read())
{
    time = reader[0].ToString();
}

Console.WriteLine(time);

Do you have any idea to make it?

Upvotes: 0

Views: 1216

Answers (1)

Alex
Alex

Reputation: 13224

How about making this a parameterized query, as in:

// Somewhere in your class declaration:
// Fixed parameterized query text as a constant.
private const string TimeRangeQuerySQL = 
    "SELECT COUNT(TimeStamp) FROM net WHERE Timestamp BETWEEN @starttime AND @endtime";

// ...
var cmd = new SqlCommand(TimeRangeQuerySQL, sqlCon);
cmd.Parameters.Add("@starttime", SqlDbType.DateTime).Value = Starttime;
cmd.Parameters.Add("@endtime", SqlDbType.DateTime).Value = Endtime;

var reader = sqlCmd.ExecuteReader();

// ...

Note that it is good practice to use parameterized queries instead of trying to assemble a query string yourself, so that you don't expose yourself to SQL injection attacks. You may want to read the story of little bobby tables.

Upvotes: 5

Related Questions