SQL中比较日期怎么写？

[snowfoxyr]

控件用的RzDateTimePicker1、RzDateTimePicker2想要按日期范围内进行检索，不知道该怎么写了，那位大侠给指点一下呢。

ADOQuery2->SQL->Clear();
ADOQuery2->SQL->Add("select * from dbo.承包合同表 where 责任人 like '%"+ADOQuery1->FieldByName("姓名")->AsString.Trim() +"%' and RzDateTimePicker1<=签订日期<=RzDateTimePicker2");
ADOQuery2->Active=true;

就是想把时间范围内的这个人所有信息都找出来，就是日期这里不知道怎么写了。
SQL数据库中的时间格式为smalldatetime

[ccrun.com]

String strSQL = String().sprintf(
    "select * from dbo.承包合同表 "
    "where [责任人] like '%%%s%' and [签订日期] between '%s 0:0:0' and '%s 23:59:59'",
    ADOQuery1->FieldByName("姓名")->AsString.Trim(),
    DateToStr(RzDateTimePicker1->Date),
    DateToStr(RzDateTimePicker2->Date));

ADOQuery2->Close();
ADOQuery2->SQL->Text = strSQL;
ADOQuery2->Open();

太神奇了，刚才这个帖子没了，现在又出现了。

[snowfoxyr]

谢谢老妖帮忙啊！！！还是一如既往的热心，呵呵。 调试没问题，但使用查询功能的时候有这个提示：将“字符串转换为smalldatetime数据类型时失败”

[ccrun.com]

smalldatetime是吧，那把 between '%s 0:0:0' and '%s 23:59:59' 换成 between '%s 0:0' and '%s 23:59' 试试？

[snowfoxyr]

String strSQL = String().sprintf( "select * from dbo.承包合同表 " "where [责任人] like '%%%s%' and [签订日期] between '%s 0:0' and '%s 23:59'", ADOQuery1->FieldByName("姓名")->AsString.Trim(), DateToStr(RzDateTimePicker1->Date), DateToStr(RzDateTimePicker2->Date)); ADOQuery2->Close(); ADOQuery2->SQL->Text = strSQL; ADOQuery2->Open(); 提示一样

[ccrun.com]

刚细看一下，代码中有一处失误，实在抱歉。应该改做：

String strSQL = String().sprintf(
    "select * from dbo.承包合同表 "
    "where [责任人] like '%%%s%%' and [签订日期] between '%s 0:0:0' and '%s 23:59:59'",
    ADOQuery1->FieldByName("姓名")->AsString.Trim(),
    DateToStr(RzDateTimePicker1->Date),
    DateToStr(RzDateTimePicker2->Date));
 
ADOQuery2->Close();
ADOQuery2->SQL->Text = strSQL;
ADOQuery2->Open();

[snowfoxyr]

呵呵，老大就是牛