问题:
select * from dbo.Tab_Article where ADate between '2009-09-14' and '2009-09-14'
假设前台文本框日历控件选择的开始是2009-09-14并且选择结束时间是2009-09-14,
那么只会查询出2009-09-14 0:00:00.000至2009-09-14 0:00:00.000的时间段数据,
但是我想要的是2009-09-14至2009-09-14 23:59:59.999之间的数据
当然最简单的方法就是在程序中的时间里用把时间转换字符串加上23:59:59.999.
private void test(String beginDate, String endDate) { DataTable dt = new DataTable(); using (SqlConnection conn = DBHelper.GetConnection()) { conn.Open(); SqlCommand comm = new SqlCommand("select * from Test where ADate between @beginDate and @endDate",conn); comm.Parameters.Add(new SqlParameter("@begindate", beginDate)); comm.Parameters.Add("@enddate", SqlDbType.DateTime,8,"Adate").Value = endDate+" 23:59:59.999"; SqlDataReader sdr = comm.ExecuteReader(); dt.Load(sdr); } GridView1.DataSource = dt; GridView1.DataBind(); }
--SQL语句还可以这样写:
select * from dbo.Tab_Article where ADate>='2009-09-14' and ADate<dateadd(dd,1,'2009-09-14')
select * from dbo.Tab_Article where ADate between '2009-09-14' and dateadd(dd,1,'2009-09-15')
select * fromdbo.Tab_Article where ADate between @startdate and dateadd(day,1,@enddate)
SQL中时间查询只能精确到秒,看下面例子得出的结果:
declare @sdt datetime ,@edt datetime
set @sdt='2009-09-14'
set @edt='2009-09-14'
select 精确毫秒=dateadd(ms,-1,dateadd(dd,1,@edt)),精确到秒=dateadd(ss,-1,dateadd(dd,1,@edt))
/*
精确毫秒 精确到秒
------------------------------------------------------ ------------------------------------------------------
2009-09-15 00:00:00.000 2009-09-14 23:59:59.000
(所影响的行数为 1 行)
*/
--所以只能精确到秒,修改如下
select * from dbo.Tab_Article where ADate between @sdt and dateadd(ss,-1,dateadd(dd,1,@edt)