sql server与access中sql的一点区别

时间：2011-06-15 来源：风过-无痕

最近在用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;