CODEWITHSUNDEEP
c#sql
user966602
user966602

Reputation: 21

SQL query for datetime

string date = DateTime.Now.AddDays(day - 1).ToShortDateString().ToString();
string count = "select count(*) from Appointment_Info where  APPT_Dt=\'" +
               Convert.ToDateTime(date) + "\'  ";
SqlCommand cc = new SqlCommand(count, cn);
int appoinments = Convert.ToInt16( cc.ExecuteScalar().ToString());

above query does not work plese see and tell is their any problems?

Upvotes: 0

Views: 4509

Answers (3)

Shadow Wizzard
Shadow Wizzard

Reputation: 66398

What you're after is such SQL instead:

DateTime dtFrom = DateTime.Now.AddDays(day - 1).Date;
DateTime dtTo = past.AddDays(1);
string strSQL = "Select Count(*) From Appointment_Info Where  APPT_Dt Between @from And @to";
int appoinments = 0;
using (SqlCommand cc = new SqlCommand(strSQL, cn))
{
    cc.Parameters.AddWithValue("@from", dtFrom);
    cc.Parameters.AddWithValue("@to", dtTo);
    appoinments = Int32.Parse(cc.ExecuteScalar().ToString());
}

Problem is you don't need exact date as it won't give you anything, you rather need range of dates meaning anything between the past date and day after it.

The above code also give better practice of using parameters and properly disposing the Command object.

Upvotes: 2

Marco
Marco

Reputation: 57593

Try this:

DateTime dt = DateTime.Now.AddDays(day - 1);
SqlCommand cc = new SqlCommand(
    "SELECT COUNT(*) FROM Appointment_Info WHERE APPT_Dt=@dt", cn);
cc.Parameters.AddWithValue("@dt", dt);

Upvotes: 0

Prashant Lakhlani
Prashant Lakhlani

Reputation: 5806

I think this will fix your problem:

    string date = DateTime.Now.AddDays(day - 1).ToShortDateString().ToString();
    string count = "select count(*) from Appointment_Info where  convert(int,convert(varchar, APPT_Dt,112))=convert(int,convert(varchar, @date,112)) ";

    SqlCommand cc = new SqlCommand(count, cn);
    cc.parameters.AddWithValue("@date",date);
    int appoinments = Convert.ToInt16( cc.ExecuteScalar().ToString());

Upvotes: 0

Related Questions