当Fromdate和Todate是相同的日期时,如何从两个日期之间的数据库中获取数据。即(2012年2月2日和2012年2月2日)。 [英] How to get data from database between two dates when Fromdate and Todate are same date. i.e (2/2/2012 and 2/2/2012).
问题描述
您好我想从两个日期之间获取数据库中的数据,其中Fromdate和Todate是相同的日期。即(2012年2月2日和2012年2月2日)。现在我正在使用这个查询
选择 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
来自 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 其中 adm.username=@username 和 amt_received_date @fromdate 和 @ todate
但是我得不到我想要的正确结果,我的后端代码是这样的。
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 {}
}
给我解决方案查询条款或C#backend
而不是尝试之间的尝试类似下面的事情
选择 std.reg_no,std.stud_name,bat.batch_name,转换( varchar ,col.amt_received_date, 103 ) as amt_received,
col.receipt_no,col.fee_amt,adm.username
来自 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 < span class =code-keyword> on std.b id = bat.bid 其中 adm.username=@username AND amt_received_date> = @fromdate 和 amt_received_date< = @ todate
一种方法是使用DATEDIFF函数:
SELECT * FROM MyTable 其中 DATEDIFF(d,amt_received_date, @ CheckDate )= 0这将在同一天返回所有DateTimes。
我建议你不要使用普通文本如果存在专用控件,则输入框。在您的情况下,您可以使用日期时间控件进行输入。
作为解决方案:
1 - 添加一天到'tod
'
tod = DateTime.AddDays( 1 );
2-或您的查询
@ fromdate和DATEADD(d,1,@ todate)
实际值:
2/2/2012 00:00:00&& 2/2/2012 00:00:00
因此你在这些DATETIME之间什么也得不到。
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
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
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.
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
well rather than using between try something like below
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
One way to do it is to use the DATEDIFF function:
SELECT * FROM MyTable where DATEDIFF(d, amt_received_date, @CheckDate)=0That will return all DateTimes in the same day.
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
'
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
这篇关于当Fromdate和Todate是相同的日期时,如何从两个日期之间的数据库中获取数据。即(2012年2月2日和2012年2月2日)。的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!