sql server与access中sql的一点区别

最近在用delphi在个程序（其实我是.net程序员，但是对.net来说也是要注意的），用sql server与access两种数据库切换，两种数据库中表示时间的字符不同，分别是#和'

开始的时候一直是用access实现，一切正常，后来开始把sql server加进来，突然发现查询时间的功能在sql server中不能使用，先通过调试分析了生成的sql 语句，好像没有问题，弄了好久最后才发现原来是条件选择日期的时候弄反了，

where pBookingdate between '2011-6-20' and '2011-6-10'

本来应该是这样的

where pBookingdate between '2011-6-10' and '2011-6-20'

但是反过来的时间在access中又是可以正确的，可以这样使用

where pBookingdate between #2011-6-20# and #2011-6-10#

看样子以后写代码的过程中还是要注意细节，养成好的习惯，可以避免不少麻烦，delphi代码

var
DaysToStr:string;
QueryStr:string;
Today:TDateTime;
SomeDayBefore:TDateTime;
SomeDayAfter:TDateTime;
SQLChar:char;
begin
if Booking.FSelectedDBType=1 then
SQLChar:='#'
else
SQLChar:=#39;
QueryStr:=ReturnBasicQuery();
DaysToStr:=IntToStr(RecentDays);
Today:=now;
SomeDayAfter:=IncDay(today,RecentDays);
SomeDayBefore:=IncDay(Today,-(RecentDays));
if (RecentDays<>0) and (Trim(Name)<>'') then
result:=QueryStr+' where pBookingDate between '+SQLChar+Datetostr(SomeDayBefore)+SQLChar+'and '+SQLChar+Datetostr(SomeDayAfter)+SQLChar+' and pName Like'+#39+'%'+Name+'%'+#39;
if (RecentDays=0) and (Trim(Name)<>'') then
result:=QueryStr+' where pName Like' +#39+'%'+Name+'%'+#39+' and pBookingDate='+SQLChar+datetostr(Date)+SQLChar;
if (RecentDays<>0) and (Trim(Name)='') then
Result:=QueryStr+' where pBookingDate between '+SQLChar+Datetostr(SomeDayBefore)+SQLChar+' and '+SQLChar+Datetostr(SomeDayAfter)+SQLChar;
if (RecentDays=0) and (Trim(Name)='') then
result:=QueryStr+' where pBookingDate='+SQLChar+datetostr(Date)+SQLChar;
end;

转载于:https://www.cnblogs.com/liaochifei/archive/2011/06/15/2081285.html