TAdoquery date format

Question

I am Java developer. I have some old program in Delphi. In old version they work with mdb. I fixed it for connection with SQL Server. All SQL queries are implemented with TAdoQuery.

qryTemp.SQL.Text:='select  sum(iif(ComeSumm>0,comesumm,0)),sum(iif(lostSumm>0,lostsumm,0)) from cash '+
   'where (IdCashClause is null or idcashclause<>8) '+
  ' and cashNum='+IntToStr(i)+
  ' and CashType=0'+
  ' and format(PayDate,"dd/mm/yyyy")=format('''+DateToStr(Date)+''',"dd/mm/yyyy") ';

The program throws an exception:

Invalid column name 'dd/mm/yyyy'.

I have fixed other query for comparison:

 qryTemp.SQL.Text:=' select top 1 iif(ComeSumm>0,comesumm,0) from cash '
                     +' where idCashReason=1 and idCashClause=8 and cashNum='+IntToStr(i)
                     +' and PayDate<:D'
                     +' order by payDate desc';
qryTemp.Parameters.ParamByName('D').Value:=DateTimeToStr(Date);

Can I quickly fix all queries for work with SQL Server without rewriting the whole project?

Answer 1

DateToStr uses localization information contained in global variables to format the date string. Maybe this is the problem.

You can try FormatDateTime:

qryTemp.SQL.Text:='select  sum(iif(ComeSumm>0,comesumm,0)),sum(iif(lostSumm>0,lostsumm,0)) from cash '+
   'where (IdCashClause is null or idcashclause<>8) '+
  ' and cashNum='+IntToStr(i)+
  ' and CashType=0'+
  ' and format(PayDate,"dd/MM/yyyy")='''+FormatDateTime('dd/mm/yyyy',Date)+'''';

Answer 2

Assuming PayDate is defined as date/datetime in MSSQL you could use parameters as follow:

qryTemp.SQL.Text:=' select top 1 iif(ComeSumm>0,comesumm,0) from cash '
                     +' where idCashReason=1 and idCashClause=8 and cashNum='+IntToStr(i)
                     +' and PayDate<:D'
                     +' order by payDate desc';
qryTemp.Parameters.ParamByName('D').Value := Date;
qryTemp.Parameters.ParamByName('D').DataType := ftDateTime;

I'd also change cashNum to parameter i.e.:

...
+' where idCashReason=1 and idCashClause=8 and cashNum=:cashNum'+
...
qryTemp.Parameters.ParamByName('cashNum').Value := i;

Always prefer to use compatible data types with your parameters, rather than formatting and using strings. SQL does not need to guess your data types if you can explicitly define them.

Note: IIF was introduced in SQL Server 2012. for older version use CASE expression.

---

In older Non-Unicode Delphi versions, Parameters have issue with Unicode.
So, If you don't use Parameters you could use the following:

function DateTimeToSqlDateTime(const DT: TDateTime): WideString;
begin
  Result := FormatDateTime('yyyy-MM-dd', DT) + ' ' + FormatDateTime('hh:mm:ss', DT);
end;

function SqlDateTimeStr(const DT: TDateTime; const Is_MSSQL: Boolean): WideString;
var
  S: WideString;
begin
  S := DateTimeToSqlDateTime(DT);
  if Is_MSSQL then
    Result := Format('CONVERT(DATETIME, ''%s'', 102)', [S]) 
  else
    Result := Format('#%s#', [S]); // MS-ACCESS
end;

And your query will look as follow:

...
+' and PayDate<' + SqlDateTimeStr(Date, True)
...

Answer 3

It´s very likely that the PayDate column is declared as DATE in the cash table. Considering that, your parameter should be a TDateTime and not a string, like this:

qryTemp.SQL.Text:=' select top 1 iif(ComeSumm>0,comesumm,0) from cash '
                     +' where idCashReason=:cashReason and idCashClause=8 and cashNum='+IntToStr(i)
                     +' and PayDate<:D'
                     +' order by payDate desc';
qryTemp.Parameters.ParamByName('D').Value := Date;

I replaced only one of the parameters, but you should consider replacing all of them, since this will improve the server performance by enabling its sentence cache.

Getting back to your original question, I guess the only way to refactor all the application would be to have a refactoring program that could parse your code, find those situations and follow a pattern to replace one piece of code by another.

I do not know any tool that can do that nowaways.

Maybe using find/replace that supports regular expressions can help you, but it certainly wont´t fix the cases in one single pass. You would have to run a series of replace phases in order to transform your code from what it is to what you want it to be.