Click here to Skip to main content
15,880,608 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I am having 2 dates that is Start date and end date from Create task table. I need to select a date.then i have to get the data from tables.the condition is that the selected date should be between the start date and end date which is in create task table. please help me
C#
SqlDataReader dr = ob.exdr("select CreateTask.Task,EmployeeDetails.Category_name,EmployeeDetails.Employee_name,TaskAssigned.Start_time,TaskAssigned.End_time,LeaveDetails.leave,CreateTask.Start_date,CreateTask.End_date from CreateTask,TaskCategoryMastertable,EmployeeDetails,LeaveDetails,TaskAssigned WHERE  CreateTask.Start_date >= '" + enterdate + "' AND CreateTask.End_date <= '" + enterdate + "'");

Here enterdate is the date which i select from the calender..
here I m getting no result.


When executing this im getting incorrect output.please anyone write query for me..the condition is that if im selecting a date which is in between the start date and end date then the datas should be displayed..please help me
Posted
Updated 26-Feb-14 21:43pm
v3
Comments
Ramug10 27-Feb-14 0:39am    
How the below table are related?
"CreateTask,TaskCategoryMastertable,EmployeeDetails,LeaveDetails,TaskAssigned"
Aravindba 27-Feb-14 0:45am    
hi pls check date format,i think u pass date that not match with in table.like in ur table u have date like "2014-02-27 11:13.00" but u pass date in where condition like "27-02-2014" it not match and u not get any result so pass correct format.
sruthyjoseph 27-Feb-14 0:57am    
both date formats are same..
and i get answer if i use OR instead of AND..
CreateTask.Start_date >= '" + enterdate + "' OR CreateTask.End_date <= '" + enterdate + "'..
but nly 1 row is getting as output..
Aravindba 27-Feb-14 1:10am    
hi if u use OR then u get answer means start or end date satisfy so it display result.i think any one date satisfy so get result,but if u use AND one date not satisfy both date so it not display result.u have try manually,change start and end date in table and try to give same date through ur application then both date satisfy then u get result.
Sibasisjena 27-Feb-14 0:43am    
convert the enterdate into date time format. check is there any record with the condition you have given in the select statement

Hi,
Your query must be like this,


SQL
select CreateTask.Task,EmployeeDetails.Category_name,EmployeeDetails.Employee_name,TaskAssigned.Start_time,TaskAssigned.End_time,LeaveDetails.leave,CreateTask.Start_date,CreateTask.End_date from CreateTask,TaskCategoryMastertable,EmployeeDetails,LeaveDetails,TaskAssigned WHERE  CONVERT(DATE,CreateTask.Start_date) between CONVERT(DATE, enterdate ) AND CONVERT(DATE, enterdate ))
 
Share this answer
 
v2
Comments
sruthyjoseph 27-Feb-14 4:19am    
WHERE CONVERT(DATE,CreateTask.Start_date) between CONVERT(DATE, enterdate ) AND CONVERT(DATE, enterdate ))..i didnt understand dis...
the date should be between start date and end date ..not between enterdate and enterdate..can u pl explain
sruthyjoseph 27-Feb-14 4:27am    
still im getting same output ...:(
Hi Sruthy,

try the below script...

SQL
SELECT
ct.Task,
ct.Start_date,
ct.End_date,
e.Category_name,
e.Employee_name,
ta.Start_time,
ta.End_time,
l.leave
FROM
EmployeeDetails e
INNER JOIN LeaveDetails l ON e.Employee_id = l.Employee_id
INNER JOIN TaskAssigned ta ON e.Employee_id = ta.Employee_id
LEFT JOIN CreateTask ct ON ta.Task_id = ct.Task_id
where ct.Start_date >= '' AND ct.End_date <= ''
 
Share this answer
 
Comments
sruthyjoseph 27-Feb-14 4:49am    
hi RG
LeaveDetails l ON e.Employee_id = l.Employee_id ..this is not needed na..as i need everyone even they are on leave or not.if we provide that condition then those who are on leave nly will b given na..so i removed it
SELECT ct.Task,ct.Start_date,ct.End_date,e.Category_name,e.Employee_name,ta.Start_time,ta.End_time,l.leave FROM EmployeeDetails e INNER JOIN LeaveDetails l INNER JOIN TaskAssigned ta ON e.Employee_id = ta.Employee_id LEFT JOIN CreateTask ct ON ta.Task_id = ct.Task_id where ct.Start_date >= '" + enterdate + "' AND ct.End_date <= '" + enterdate + "'

i write it as this after removing that cndition..nw im getting an error:incorrect syntax near where..
sruthyjoseph 27-Feb-14 5:42am    
how to correct this??
sruthyjoseph 27-Feb-14 4:50am    
hi RG
thanks for spending ur valuable time for me....
I think its a logic error.NOT a Conversion Error

In ur code u r checking

SQL
WHERE  CreateTask.Start_date >= '" + enterdate + "' AND CreateTask.End_date <= '" + enterdate + "'");



consider ur table having start date as 01-10-2011 and end date as 01-20-2011
and ur entry date is 01-14-2011

from ur query u check

01-10-2011>=01-14-2011(always false except start date=enter date)
AND
01-20-2011<=01-14-2011(always false except end date=enter date)


So plz change query to

SQL
WHERE  CreateTask.Start_date <= '" + enterdate + "' AND CreateTask.End_date >= '" + enterdate + "'");


OR

SQL
WHERE '" + enterdate + "' BETWEEN CreateTask.Start_date  AND CreateTask.End_date);
 
Share this answer
 
Comments
sruthyjoseph 27-Feb-14 23:35pm    
SqlDataReader dr = ob.exdr("select CreateTask.Task,EmployeeDetails.Category_name,EmployeeDetails.Employee_name,TaskAssigned.Start_time,TaskAssigned.End_time,LeaveDetails.leave from CreateTask,EmployeeDetails,LeaveDetails,TaskAssigned WHERE '" + enterdate + "'>= CreateTask.Start_date AND '" + enterdate + "' <=CreateTask.End_date AND EmployeeDetails.Employee_id=TaskAssigned.Employee_id AND TaskAssigned.Task_id=CreateTask.Task_id");


now im getting correct answer but only 1 row is returning..im using table(bcs i cant use gridview).I think only 1 row can be returned using my code .please change my code such that all the matching rows can be displayed in table..
My code is this:
protected void ImageButton1_Click(object sender, ImageClickEventArgs e)
{


ba.enterdate = Convert.ToDateTime(TextBox1.Text);
SqlDataReader dt = ba.TaskListingSelect5();
Table t = new Table();
TableRow tr = new TableRow();
TableHeaderCell t1 = new TableHeaderCell();
t1.Text = "Category Name";
TableHeaderCell t2 = new TableHeaderCell();
t2.Text = "Task";
TableHeaderCell t3 = new TableHeaderCell();
t3.Text = "Employee Name";
TableHeaderCell t4 = new TableHeaderCell();
t4.Text = "Start time";
TableHeaderCell t5 = new TableHeaderCell();
t5.Text = "End time";

TableHeaderCell t6 = new TableHeaderCell();
t6.Text = "Leave status";

TableCell c1 = new TableCell();
TableCell c2 = new TableCell();
TableCell c3 = new TableCell();
TableCell c4 = new TableCell();
TableCell c5 = new TableCell();
TableCell c6 = new TableCell();
//int count = dt.FieldCount;



while (dt.Read())
{

//for (int i = 0; i < count; i++)
// {
c1.Text= dt.GetValue(0).ToString();
c2.Text = dt.GetValue(1).ToString();
c3.Text=dt.GetValue(2).ToString();
c4.Text= dt.GetValue(3).ToString();
c5.Text = dt.GetValue(4).ToString();
c6.Text = dt.GetValue(5).ToString();
// }


}



tr.Cells.Add(c1);
tr.Cells.Add(c2);
tr.Cells.Add(c3);
tr.Cells.Add(c4);
tr.Cells.Add(c5);
tr.Cells.Add(c6);
t.Rows.Add(tr);
Master.FindControl("ContentPlaceHolder1").Controls.Add(t);

}
george4986 27-Feb-14 23:45pm    
whats the content of 'dt'?
is it returning correct records from DB?
sruthyjoseph 27-Feb-14 23:57pm    
wen i execute it using break point,it is showing dat dt is returning null..but iam getting 1 row as output..
(break point in 2nd line)
george4986 28-Feb-14 0:04am    
whats the value of 'dt.Rows.Count'?
sruthyjoseph 28-Feb-14 0:20am    
im not able to find it..dt.HasRows is nly available..
if im telling some blunders plz correct me

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900