問題：

我的表某個字段是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'