date query question

I have the following database:

[id] [int] IDENTITY(1,1) NOT NULL,
[id_number] [varchar](50) NULL,
[company_reg_number] [int] NULL,
[resourceid] [int] NULL,
[status] [int] NULL,
[subject] [varchar](50) NULL,
[description] [text] NULL,
[label] [int] NULL,
[starttime] [datetime] NULL,
[endtime] [datetime] NULL,
[schedule_type] [varchar](255) NULL,
[location] [varchar](50) NULL,
[allday] [bit] NULL,
[eventtype] [int] NULL,
[recurrenceinfo] [text] NULL,
[reminderinfo] [text] NULL,
[staff_file_no] [int] NULL,

and I want to query the date range between starttime and endtime. I have created this code in C# but I don't know how to create

the stored procedure:

private void getScheduleList()
{
data_table = new DataTable();
// sql_connection = new SqlConnection("Data
Source=.\\SQLEXPRESS;initial catalog=shefa;integrated security=true");
sql_connection = new SqlConnection(public_var.sql_connection);

sql_command = new SqlCommand("sp_get_staff_schedule", sql_connection);
sql_command.CommandType = CommandType.StoredProcedure;
sql_command.Parameters.Add("@.id_number", SqlDbType.VarChar).Value = cboDoctor.Text.Substring(0, 9);

sql_command.Parameters.Add("@.starttime", SqlDbType.DateTime).Value = dateSchedule.Value;

if (cboRange.Text == "DAY")
sql_command.Parameters.Add("@.endtime", SqlDbType.DateTime).Value = dateSchedule.Value;
else if (cboRange.Text == "WEEK")
sql_command.Parameters.Add("@.endtime", SqlDbType.DateTime).Value = dateSchedule.Value.AddDays(7);
else if (cboRange.Text == "MONTH")
sql_command.Parameters.Add("@.endtime", SqlDbType.DateTime).Value = dateSchedule.Value.AddDays(31).ToShortDateString();
else
sql_command.Parameters.Add("@.endtime", SqlDbType.DateTime).Value = dateSchedule.Value;

data_adapter = new SqlDataAdapter(sql_command);
data_adapter.Fill(data_table);
dataSchedule.DataSource = data_table;

dataSchedule.Columns[0].HeaderText = "Type";
dataSchedule.Columns[0].DefaultCellStyle.Alignment = DataGridViewContentAlignment.TopLeft;
dataSchedule.Columns[0].Width = 180;

dataSchedule.Columns[1].HeaderText = "Staff Name";
dataSchedule.Columns[1].DefaultCellStyle.Alignment = DataGridViewContentAlignment.TopLeft;
dataSchedule.Columns[1].Width = 180;

dataSchedule.Columns[2].HeaderText = "From";
dataSchedule.Columns[2].DefaultCellStyle.Alignment = DataGridViewContentAlignment.TopCenter;
dataSchedule.Columns[2].DefaultCellStyle.Format = "ddd dd MMM yyyy HH:mm";
dataSchedule.Columns[2].Width = 130;

dataSchedule.Columns[3].HeaderText = "To";
dataSchedule.Columns[3].DefaultCellStyle.Alignment = DataGridViewContentAlignment.TopCenter;
dataSchedule.Columns[3].DefaultCellStyle.Format = "ddd dd MMM yyyy HH:mm";
dataSchedule.Columns[3].Width = 130;
}

NOTE: I only want to create the date part (time must be ignored) so if user selected dates between 9/11/2007 and 9/12/2007 it should list all records regardless of the time.

Please post the stored procedure () code so that we may better help you.

Most likely, you want the WHERE clause to be something like this:

WHERE ( StartTime >= ( dateadd( day, 0, datediff( day, 0, @.StartTime )))

AND EndTime < ( dateadd( day, 0, datediff( day, 0, ( @.StartTime + 1 ))))

)

|||

You can Use " CONVERT | CAST " to make the data's format be consistency.

such as your case, you can trythe sample code as below:

Where 1=1

and convert (char,starttime,103) >= @.starttime

and convert (char,endtime,103) <= @.endtime

maybe it won't get the best efficiency.

hoping this can help u.

Best Regrades,

Hunt .

|||

One of the significant problems with using CAST() or CONVERT() in that manner, is that when you wrap the column with a function like that, it negates the possibility of using indexing.

As Hunt indicated -"it won't get the best efficinecy." In fact, if the table is large, it will be totally frustrating

|||

I've posted a better solution here. It's faster and will use indexes when they're present.

Sean

sql