Subject RE: [firebird-support] SQL conundrum
Author Clay Shannon
-----Original Message-----
From: Ivan Prenosil [mailto:Ivan.Prenosil@...]

<<You can cast TIMESTAMP to both DATE and TIME (dialect-3 only):

where (id > 305)
and cast (p.PCHIN as date) between '2005/4/5' and '2005/4/7'
and cast (p.PCHIN as time) between '13:00:00.0000' and '23:59:59.9999'>>

Well, after much flailing about, I was finally able to get it to work as I
wanted. Oddly, I could not get the Time values as Parameters, but had to use

sCoreSQL = 'select e.fullname, d.deptname, p.dayofweek, p.pchin, '+
'p.pchinloc, p.pchout, p.pchoutloc '+
'from pchup p '+
'join empinfo e on e.empno = p.empno '+
'join dept d on d.deptno = p.pchdept '+
'where (id > 305) '+
'and cast (p.PCHIN as date) between :FromDate and :ToDate '+
'and cast (p.PCHIN as time) between %s and %s '+
'order by p.pchin';
. . .
sTimeFrom := FormatTimeAsSQLTime(dtpTimeFrom.DateTime);
sTimeTo := FormatTimeAsSQLTime(dtpTimeTo.DateTime);
FIBDataset.SQLs.SelectSQL.Add(Format(sCoreSQL, [
QuotedStr(sTimeFrom), QuotedStr(sTimeTo)]));

{ Date/Time range }
FIBDataset.ParamByName('FromDate').AsDate := dtpDateFrom.Date;
FIBDataset.ParamByName('ToDate').AsDate := dtpDateTo.Date;

function FormatTimeAsSQLTime(ADateTime: TDateTime): String;
wHours, wMinutes, wSeconds, wMilliseconds: Word;
DecodeTime(ADateTime, wHours, wMinutes, wSeconds, wMilliseconds);
Result := Format('%.2d:%.2d:%.2d.0000', [wHours, wMinutes, wSeconds]);

Clay Shannon,
Dimension 4 Software