Devart Forums

Posted: **Fri 23 Dec 2011 00:17**

Hi,

I have a query where I'm trying to extract certain records based on the time stamp of the record entries on MySQL.

The database field is declared as a type "DATETIME", which contains a date and time stamp of a record's creation date.

The SQL query contains parameters where I'm filtering according to a given date and a time range. When I run the query on MySQL server itself with a SQL manager tool, I get the desired results, but when I execute the exact same query using the "TUniQuery" component, it seems that the "time" filter parameter is ignored completely??

Here's a sample query which I'm executing on a InnoDB table where I'm trying to filter out record results based on the time of one specific field which is of type "DATETIME" containing a complete date and time stamp. Also note that the field I'm filtering on the date alone, is a "DATE" only field:

Code: Select all

SELECT t1.DateField. t2.DateTimeField
FROM Table1 AS t1
INNER JOIN Table2 AS t2
ON t1.ID = t2.ID
WHERE (t1.DateField BETWEEN '2011-06-15' AND '2011-06-16')
AND (CAST(t2.DateTimeField AS TIME) BETWEEN '10:51:00' AND '10:52:00')
GROUP BY t1.DateField
ORDER BY t1.DateField, CAST(t2.DateTimeField AS TIME)

From within Delphi XE2, I execute the query the following way:

SQL Query assigned to the UniQuery component"

Code: Select all

SELECT t1.DateField. t2.DateTimeField
FROM Table1 AS t1
INNER JOIN Table2 AS t2
ON t1.ID = t2.ID
WHERE (t1.DateField BETWEEN :pStartDate AND :pEndDate)
AND (CAST(t2.DateTimeField AS TIME) BETWEEN :pStartTime AND :pEndTime)
GROUP BY t1.DateField
ORDER BY t1.DateField, CAST(t2.DateTimeField AS TIME)

Code: Select all

       
var
  StartDate, EndDate : TDate;
  StartTime, EndTime : TTime;

begin
  StartDate := SelectStartDate.Date;
  EndDate   := SelectEndDate.Date;
  StartTime := SelectStartTime.Time;
  EndTime   := SelectEndTime.Time;

  with UniQuery1 do begin  
    Close;
    ParamByName('pStartDate').AsDate := StartDate;
    ParamByName('pEndDate').AsDate := EndDate;
    ParamByName('pStartTime').AsTime := StartTime;
    ParamByName('pEndTime').AsDate := EndTime;
    Open;
  end;
end;

Again, this query produces the desired results when executed directly on the server, but completely ignores the time range given in the "time" parameters within the query.

Any suggestions why this is happening??

I'm running the following versions of software:

- Delphi XE2 Update 2
- MySQL 5.1.51 Community Edition.
- UniDAC 4.0.2

Thanks.

Posted: **Fri 23 Dec 2011 12:41**

Hello,

Please specify the types of the SelectStartDate and SelectEndDate variables.

Devart Forums

TUniQuery Ignoring Time Based Parameters In MySQL Query??

TUniQuery Ignoring Time Based Parameters In MySQL Query??