SQL中Between语句查询日期

问题：

我的表某个字段是Datetime型 以" YYYY-MM-DD 00:00:00" 存放
如
A 2009-01-22 21:22:22
B 2009-01-22 19:21:11
C 2009-01-22 23:10:22
现在用 select * from TABLE where date between '2009-1-22' And '2009-1-22' 想查日期为2009-1-22的记录 结果查不到
有什么办法吗

解决：

   1: create table tb(id varchar(1),riqi datetime)
   2: insert into tb values('A' , '2009-01-22 21:22:22') 
   3: insert into tb values('B' , '2009-01-22 19:21:11') 
   4: insert into tb values('C' , '2009-01-22 23:10:22')
   5: go
   6: --1
   7: select * from tb where convert(varchar(10),riqi,120) = '2009-01-22'  
   8: /*
   9: id   riqi                                                   
  10: ---- ------------------------------------------------------ 
  11: A    2009-01-22 21:22:22.000
  12: B    2009-01-22 19:21:11.000
  13: C    2009-01-22 23:10:22.000
  14: 
  15: （所影响的行数为 3 行）
  16: */
  17:  
  18: --2
  19: select * from tb where riqi between '2009-01-22 00:00:00' and '2009-01-22 23:59:59'  
  20: /*
  21: id   riqi                                                   
  22: ---- ------------------------------------------------------ 
  23: A    2009-01-22 21:22:22.000
  24: B    2009-01-22 19:21:11.000
  25: C    2009-01-22 23:10:22.000
  26: 
  27: （所影响的行数为 3 行）
  28: */
  29:  
  30: drop table tb

短日期类型默认Time为00:00:00，所以当使用between作限制条件时，就相当于between '2009-1-22 00:00:00'  and '2009-1-22 00:00:00',因此就查不出数据。要想实现功能，那就使用连接字串的形式，在短日期后面把时间补全，那样就能实现功能了。

解决方案：
 1: date1 between '" + dateTimePicker1.Value.ToShortDateString() + " 00:00:00' and '" + dateTimePicker2.Value.ToShortDateString() + " 23:59:59'