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?

Since your SQLServer supports IIF you seem to be on version 2012. Never the less you will have to fix all/most of the queries, which would be an oaccasion to switch to parameters, as you did for PayDate, in any place, here e.g. for cashNum='+IntToStr(i). Maybe you could move all SQLs as consts to a single (resoure)file for futur adjustments. — bummi, Nov 08 '13 at 06:56
Thanks. I am replace all params with date, trough `qryTemp.Parameters.ParamByName('D').Value:=DateTimeToStr(Date);` — Rinat Mukhamedgaliev, Nov 08 '13 at 07:42
what is the column definition for PayDate, is it datetime or a string?? — whosrdaddy, Nov 08 '13 at 07:59

score 4 · Accepted Answer · edited Dec 22 '14 at 15:28

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)
...

score 2 · Answer 2 · answered Nov 08 '13 at 09:41

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.

David Miró · Answer 3 · 2013-11-08T07:55:01.040

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)+'''';

edited Nov 08 '13 at 07:55

answered Nov 08 '13 at 07:42

David Miró

2,694
20
20

I think the date format is incorrect in "format"!. It must be 'dd/MM/yyyy' instead of 'dd/mm/yyyy'. Because **mm** are minutes. Rectified. Try again. – David Miró Nov 08 '13 at 07:53
I think you're wrong. FORMAT is a new feature of SQL Server 2012. **MM** are months. http://msdn.microsoft.com/es-es/library/ee634398.aspx @whosrdaddy – David Miró Nov 08 '13 at 08:21
the thing is that if paydate is a datetime column, the query should work with parameters, not this format hell – whosrdaddy Nov 08 '13 at 09:07
Surely. But I prefer to decide it ;-) – David Miró Nov 08 '13 at 09:13
`FORMAT` is a new feature include in MS SQL 2012. If your version of MS SQL is earlier, try with **Convert**. An example (In spanish, sorry): http://www.3engine.net/wp/2011/09/convertir-un-datetime-a-cadena-formateada-en-sql-server/ – David Miró Nov 08 '13 at 10:05

TAdoquery date format

3 Answers3

Linked