Click here to Skip to main content
15,899,475 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi i want to get the data from database between two dates where Fromdate and Todate are same date. i.e (2/2/2012 and 2/2/2012). right now i'm using this query

SQL
select std.reg_no,std.stud_name,bat.batch_name,convert(varchar,col.amt_received_date,103) as amt_received,
col.receipt_no,col.fee_amt,adm.username
 from tbl_collections col join tbl_admin adm
on adm.id=col.id join tbl_student std on col.sid=std.sid join
tbl_batches bat on std.bid=bat.bid where adm.username=@username AND amt_received_date between @fromdate and @todate


but i'm not getting the correct result as i want and my back end code is like this.


C#
protected void onselect_change(object sender, EventArgs e)
{
    try
    {
        string fdate = txtCalendar.Text.ToString();
        string tdate = txtCalendar1.Text.ToString();
        DateTime fromd = DateTime.Parse(fdate, System.Globalization.CultureInfo.InvariantCulture);
        DateTime tod = DateTime.Parse(tdate, System.Globalization.CultureInfo.InvariantCulture);
        string user = cashier_list.SelectedItem.Text;
        DataSet ds2 = new DataSet();
        ds2 = obj.fee_collectionsreport(3, 0, user, fromd, tod);
        collection_grid.DataSource = ds2;
        collection_grid.DataBind();
    }
    catch { }
}

give me solution in terms of query OR C# backend
Posted
Updated 24-Jan-14 1:23am
v2

well rather than using between try something like below


SQL
select std.reg_no,std.stud_name,bat.batch_name,convert(varchar,col.amt_received_date,103) as amt_received,
col.receipt_no,col.fee_amt,adm.username
 from tbl_collections col join tbl_admin adm
on adm.id=col.id join tbl_student std on col.sid=std.sid join
tbl_batches bat on std.bid=bat.bid where adm.username=@username AND amt_received_date >= @fromdate and amt_received_date <= @todate
 
Share this answer
 
v2
One way to do it is to use the DATEDIFF function:
SQL
SELECT * FROM MyTable where DATEDIFF(d, amt_received_date, @CheckDate)=0
That will return all DateTimes in the same day.
 
Share this answer
 
I suggest you not to use ordinary text box for an input if there exists a dedicated control. In your case, you can use datetime control for the input.
As solution:
1-add a day to 'tod'
C#
tod = DateTime.AddDays(1);

2- or your query
@fromdate and DATEADD(d,1,@todate)


actual values:
2/2/2012 00:00:00 && 2/2/2012 00:00:00
therefore you get nothing between those DATETIMEs.

http://technet.microsoft.com/en-us/library/ms186819.aspx[^]

http://msdn.microsoft.com/en-us/library/system.datetime.adddays%28v=vs.110%29.aspx[^]

ps: google
 
Share this answer
 
try this.

SQL
select std.reg_no,std.stud_name,bat.batch_name,convert(varchar,col.amt_received_date,103) as amt_received,
col.receipt_no,col.fee_amt,adm.username
 from tbl_collections col join tbl_admin adm
on adm.id=col.id join tbl_student std on col.sid=std.sid join
tbl_batches bat on std.bid=bat.bid 
where adm.username=@username AND 
(amt_received_date>=@fromdate and amt_received_date<=@todate)
 
Share this answer
 

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