Create Task if Selected Date is in between Start and End Date

Question

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 26-Feb-14 18:33pm

sruthyjoseph

Updated 26-Feb-14 21:43pm

v3

Add a Solution

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

sruthyjoseph 27-Feb-14 0:58am

date format is correct and there is many records with the matching conditions...
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..

Ramug10 27-Feb-14 1:04am

on what date you getting one record?
Convert enterdate to datetime and try once.

sruthyjoseph 27-Feb-14 1:10am

i tried converting to datetime but still no use :(

Ramug10 27-Feb-14 1:19am

could you join tables "CreateTask,TaskCategoryMastertable,EmployeeDetails,LeaveDetails,TaskAssigned" keep left join for CreateTask table.

sruthyjoseph 27-Feb-14 3:26am

can u plz help me by writing query to do d same..plz

sruthyjoseph 27-Feb-14 3:29am

I need to select a date from calender.This selected date is stored in a textbox and its value is stored in the variable 'enterdate'.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 write the code for the above problem.my above code is returning incorrect output

Ramug10 27-Feb-14 3:43am

show me all table structures so that I can able to write join query..
-RG

sruthyjoseph 27-Feb-14 3:57am

hi RG this are my tables and fields
1)CreateTask :Task_id,Category_id,Work_mode_id,Task,Start_date,End_date,Description
2)EmployeeDetails:Employee_id,Employee_name,Address,Category_name
3)LeaveDetails:Leave_id,Employee_id,Date,No-of_days,leave
4)TaskAssigned:Task_id,Employee_id,Assigned_date,Start_time,End_time,Duration

these are my tables.here leave is actually leave staus that is ' on leave'

george4986 27-Feb-14 6:09am

whats the data type of 'enterdate'?

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Blesson Mathew · Answer 1 · 2014-02-26T22:04:00

Solution 1

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 ))

Posted 26-Feb-14 22:04pm

Blesson Mathew

Updated 26-Feb-14 22:05pm

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 ...:(

Ramug10 · Answer 2 · 2014-02-26T22:28:00

Solution 2

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 <= ''

Posted 26-Feb-14 22:28pm

Ramug10

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....

george4986 · Answer 3 · 2014-02-27T01:29:00

Solution 4

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);

Posted 27-Feb-14 1:29am

george4986

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

george4986 28-Feb-14 0:23am

sorry my mistake i thought dt means 'datatable' its datareader right?

sruthyjoseph 28-Feb-14 0:36am

ya its datareader :)

george4986 28-Feb-14 0:45am

u have specified value for header cell , but header row not added .Is it ok?

comment these lines in ur code
TableRow tr = new TableRow();
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);

un comment these commented lines.
//int count = dt.FieldCount;
//for (int i = 0; i < count; i++)
// {
// }

inside for loop try these lines
TableRow tr = new TableRow();
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);

sruthyjoseph 28-Feb-14 0:55am

i dnt knw whrther header row is needed or not..im new in using tables..i always uses gridview.
i tried as u said but still nly 1 row is getting

george4986 28-Feb-14 1:20am

check this link
http://forums.asp.net/t/840386.aspx?Dynamically+adding+contents+to+ContentPlaceHolder

sruthyjoseph 28-Feb-14 4:00am

SqlDataSet dr = ob.exdat("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");

I changed datareader to dataset..bcs i thnk datareader can reaturn nly 1 row..
now the above code line shows error:Cannot implicitly convert type 'system.Data.Dataset' to 'mainprjct2.SqlDataSet'

here mainprjct2 is the name f my project

Please help me

george4986 28-Feb-14 4:34am

data reader can return multiple rows .Thats not the problem

i can't run ur code here .check below code section with ur code

SqlCommand command = new SqlCommand("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;", connection);
connection.Open();

SqlDataReader reader = command.ExecuteReader();
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";

TableHeaderRow header = new TableHeaderRow(); // Creating a header row
header.Cells.Add(t1);
header.Cells.Add(t2);
header.Cells.Add(t3);
header.Cells.Add(t4);
header.Cells.Add(t5);
header.Cells.Add(t6);
t.Rows.Add(header);
int count = reader.FieldCount;
if (reader.HasRows)
{
while (reader.Read())
{
TableRow tRow = new TableRow();

for (int i = 0; i < count; i++)
{
TableCell tempCell = new TableCell();
tempCell.Text = reader.GetValue(i).ToString();
tRow.Cells.Add(tempCell);
}
t.Rows.Add(tRow);

}
}
else
{
Console.WriteLine("No rows found.");
}
reader.Close();

sruthyjoseph 28-Feb-14 5:03am

wen executing your code no result is getting :(

george4986 28-Feb-14 5:09am

use break points debug code .make sure the query returns value from database