CODEWITHSUNDEEP
c#sqlsql-server
naouf
naouf

Reputation: 637

How do I get number of rows between two Dates/Times in SQL table?

How can get the number of rows between two dates and between two times in SQL database from my windows form application in c#?

screenShot

As shown in the screenshot (SQL database), and using the following query I am trying to get the number of rows between two certain dates and times:

For example: The dates/times starts from today at 13:00:00 until now.

int NumberOfRows;
 SqlConnection con = new SqlConnection("Data Source= pcn1;Initial Catalog=mydb;Integrated Security=True");
 SqlDataAdapter sda = new SqlDataAdapter("SELECT * FROM Orders WHERE DateTime BETWEEN '" + DateTime.Today.AddHours(13).ToString("yyyy-MM-dd HH-mm-ss") + "' AND '" + DateTime.Now.ToString("yyyy-MM-dd HH-mm-ss") + "'  ", con);

 DataTable dt = new DataTable();
 sda.Fill(dt);
 NumberOfRows= dt.Rows.Count;

The problem is: when I execute this query under button click it shows error "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value". When I change the query format from yyyy-MM-dd HH-mm-ss to yyyy-MM-dd it works but it returns number of row = 0 (it should return 3) .

Anything I am doing wrong? Please help how to get the number of rows between certain dates/times as shown in the query.

Upvotes: 0

Views: 772

Answers (4)

Nayas Subramanian
Nayas Subramanian

Reputation: 2379

This worked for me

string myScalarQuery = "select count(*) from TableName where WHERE DateTime BETWEEN @from AND @to";

SqlCommand myCommand = new SqlCommand(myScalarQuery, myConnection);
mycommand.Parameters.AddWithValue("@from",fromDate);
mycommand.Parameters.AddWithValue("@to",toDate);

myCommand.Connection.Open();
int count = (int) myCommand.ExecuteScalar();
myConnection.Close();

Upvotes: 1

Evgeni Dimitrov
Evgeni Dimitrov

Reputation: 22506

    sda = new SqlDataAdapter("SELECT * FROM Orders WHERE DateTime BETWEEN @from AND @to",con);
    sda.SelectCommand.Parameters.AddWithValue("@from",fromDate);
    sda.SelectCommand.Parameters.AddWithValue("@to",toDate);

fromDat and toDate are two DateTime objects;

Upvotes: 1

juharr
juharr

Reputation: 32296

Instead of formatting the DateTime values and concatenating them into the query string just use parameters.

SqlCommand command = con.CreateCommand();
command.CommandText = "SELECT * FROM Orders WHERE DateTime BETWEEN @FromDate AND @ToDate";
command.Parameters.Add("@FromDate", SqlDbType.DateTime).Value = DateTime.Today.AddHours(13);
command.Parameters.Add("@ToDate", SqlDbType.DateTime).Value = DateTime.Now;
SqlDataAdapter sda = new SqlDataAdapter(command);

Upvotes: 3

Akshey Bhat
Akshey Bhat

Reputation: 8545

SqlDataAdapter sda = new SqlDataAdapter("SELECT * FROM Orders WHERE DateTime BETWEEN '" + DateTime.Today.AddHours(13).ToString("yyyyMMdd HH:mm:ss") + "' AND '" + DateTime.Now.ToString("yyyyMMdd HH:mm:ss") + "'  ", con);

make changes in code as above and try.

Upvotes: 1

Related Questions