List:General Discussion« Previous MessageNext Message »
From:Noamn Date:December 18 2003 1:46pm
Subject:RE: Comparing date fields (Delphi programmers take note)
View as plain text  
Thanks for all those who helped me with this a few days ago (I've only now
had the chance to get back to programming).

As someone correctly surmised, I am using Delphi and connecting to mySQL via
a TQuery (actually a TZQuery, via Zeos components). What I have discovered
is that
1. mySQL doesn't like queries with date parameters (one must pass the date
as a literal)
2. the date format has to be changed to that of mySQL.
3. if one uses a table alias ("p" in my case), one is not allowed to use the
full table name - "projects.closeddate" causes an error, but p.closeddate is
ok.

So I wrote a small function (called date2sql) which turns 18/12/2003 into
"2003-12-18" (complete with double quotation marks), and then in my code I
write
with query1 do
begin
 close;
 sql[3]:= 'where p.closeddate between ' + date2sql (edFromDate.text) + ' and
' + date2sql (edTillDate.text);
 open
end;

This works as it should.

-----Original Message-----
From: Martijn Tonies [mailto:m.tonies@stripped]
Sent: Tuesday, December 16, 2003 4:47 PM
To: mysql@stripped
Subject: Re: Re[2]: Comparing date fields

> mysql>> select projects.name from projects
> JB>     -> where projects.closeddate between :fd and :td;
> JB> ERROR 1064: You have an error in your SQL syntax near ':fd and :td' at
> JB> line
> JB> 2
> JB> What are those colons?
>
> He probably uses Delphi or C++Builder to connect to mysql via TQuery
> component. TQuery has a property SQL where sql statement is stored.
> The colon ':' means that fd is a parameter whose value should be
> inserted into TQuery.SQL statement just before executing the query.
>
> The solution is to substitute :fd with a real value directly by accessing
TQuery.SQL
> property.
> Query.SQL.Clear;
> Query.SQL.Add('select ...');
> Query.SQL.Add('where projects.closeddate between 01/01/01 and 01/01/02');
> Query.Open;

I very much doubt this works:

01 divided by 01 :-)

I guess you wanted to write:

'01/01/01'

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=1
Thread
RE: Comparing date fields (Delphi programmers take note)Noamn18 Dec
  • Re: Comparing date fields (Delphi programmers take note)Martijn Tonies18 Dec
  • Countries, cities, states databaseAugusto Cesar Castoldi18 Dec
    • Re: Countries, cities, states databaseBernard Clement18 Dec