Problem with a query

Problem with a query

Postby classicmydac » Wed 04 Jan 2006 11:42

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
classicmydac
 
Posts: 38
Joined: Fri 23 Dec 2005 11:51

Postby tmezzena@gmail.com » Wed 04 Jan 2006 12:35

Hi,

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

Tato.
tmezzena@gmail.com
 
Posts: 6
Joined: Fri 16 Dec 2005 22:33

Postby Ikar » Wed 04 Jan 2006 14:42

> (:today - ISSUED_DATE) > 14
You should use Date functions. Please see MySQL reference for details.
Ikar
 
Posts: 1693
Joined: Thu 28 Oct 2004 13:56

Job done

Postby classicmydac » Wed 04 Jan 2006 15:14

Many thanks.

Lot to learn as I go along......
classicmydac
 
Posts: 38
Joined: Fri 23 Dec 2005 11:51


Return to MySQL Data Access Components