Page 1 of 1

Problem with a query

Posted: Wed 04 Jan 2006 11:42
by classicmydac
I have upgraded from Paradox tables to MySql. The following query worked before but does not bring back any results.

franchisee := edit5.Text;
if franchisee = '199' then temp := 'SELECT * FROM Tickets WHERE (:today - ISSUED_DATE) > 14 AND Ticket_Status = "In Process" ORDER BY Registration_No DESC ';
if franchisee '199' then temp := 'SELECT * FROM Tickets WHERE (:today - ISSUED_DATE) > 14 AND Ticket_Status = "In Process" AND Belongs_Number = "'+franchisee+'" ORDER BY Registration_No DESC ';

Datamodule7.Query1.SQL.Text := temp;
Datamodule7.Query1.ParamByName('today').AsDateTime := Date();
Datamodule7.Query1.Open;

Is this a language issue or are the field types different.


Cheers

SteveW

Posted: Wed 04 Jan 2006 12:35
by tmezzena@gmail.com
Hi,

You have to use date functions to work with dates in MySql.
change:
(:today - ISSUED_DATE)
to:
DATEDIFF(:today,ISSUED_DATE)

Tato.

Posted: Wed 04 Jan 2006 14:42
by Ikar
> (:today - ISSUED_DATE) > 14
You should use Date functions. Please see MySQL reference for details.

Job done

Posted: Wed 04 Jan 2006 15:14
by classicmydac
Many thanks.

Lot to learn as I go along......