查询时间段数据问题

问题:

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)