SYSDATE in SQL

[JonnyBee]

I am trying to compare the sysdate to a field value. The SQL I have is as follows:

SELECT COUNT(CardNo)
FROM "Clients.DB" Clients
WHERE ConExpiry >= Sysutils.date;

This doesn't work. What do I put in place of Sysutils.date?

 

[petertickler]

Is it now picking up any records at all?

You can pass a date via a parameter of course. Things you need to be aware of is the format of your date and are you storing the time. For example, in one of my programs I want to pick up a single day's transactions. However because all my dates are stored as datetime records, a straight = sign does not work. Therefore when I call run my Tquery, the code is like this.

with dmLegalCash.quLegalCash do begin
if Active then Close;
ParamByName('FromDate').AsDateTime := FromDate -1;
ParamByName('EndDate').AsDateTime := EndDate;
Open;
end;

Another thing to be check is the format of your dates. I discovered when manually constructing some SQL in my intranet system that the Database expected the date in the order Month, Day, Year, not Day, Month, Year as I expected. So my code looks like this:

SQL.add('select DISTINCT Date, Cast(Notes as Char(300)) as Notes, ContactMade.ContactMade_ID, ContactMade.UserName as userName, ContactName, ResponseDate, ClosureDate, ReferredTo, Status, ludept.deptname, luUser.username from contactMade,');
SQL.add(' luUser, luDept where Date >= ''' + request.contentFields.Values['FromMM'] + '/' + request.contentFields.Values['FromDD'] + '/' + request.contentFields.Values['FromYY'] + '''');

If this doesn't help, you really need to provide more detail!

Peter Tickler