Compare Date using ADO and Access

[topcat01]

Hi All,

I have an Access database and using ADOQuery, Delphi 6 Pro:

I have a field called 'ExpireDate' in Access I have set this to a ShortDate.

I want to only display records in a dbgrid where the expiredate is less than todays date.

I have looked at previous posts on the forum but they do not work. Here is my code:

with ADOQuery1 do
begin
Close;
SQL.Clear;
SQL.Add('SELECT * FROM TestTable WHERE ExpireDate < ' +FormatDateTime('mm"/"dd"/"yyyy', Now));
Open;
end;

I have tried formatting the date (as above) as I've read that SQL stores the date in US format, does that make a difference? My computer is set to UK dd/mm/yyyy

Thanks for your time and help.

 

[ChrisGSchultz]

I get around the variety of date formats by using parameters in my SQL for these queries.
Therefore, I'll set a parameter with the date I'm working with, and use that date, encoded, as the date in my query:

  with ADOQuery do begin
    Close;
     Parameters.ParamByName('startdate').Value :=  encodedate(yearof(DT_Pick_YearStart.date),7,1); 
     Parameters.ParamByName('enddate').value := encodedate(yearof(DT_Pick_MonthEnd.date),monthof(DT_Pick_MonthEnd.Date),dayof(DT_Pick_MonthEnd.Date));
     Prepared := TRUE;
   Open; // open the query
  end; // with ADOQuery_CSV

These use a DatePicker component for the dates, and two parameters - [tt]StartDate[/tt] and [tt]EndDate[/tt]

The query is then something like:

  with MyQuery do begin
    close;
    SQL.Clear;  // build the main statement
    SQL.Add('  SELECT * FROM TestTable';
    SQL.Add(' WHERE ((ExpireDate BETWEEN :startdate AND :enddate)'); 
// or > [b]:startdate[/b] - anything with a [b]:[/b] preceding it is a parameter in the SQL
    SQL.Add('ORDER BY (LastName + FirstName);');

// add the parameters 
// WARNING: the Parameter object changes name with nonADO and ADO queries - PARAMETERS in ADO, PARAMS  in nonADO
// PARAMS and PARAMETERS have different var list too
    Parameters.Clear;  
    // set the extraction date range
    with Parameters.CreateParameter( 'startdate', ftDateTime, ptInputOutput) do
      AsDate := DTP_StartDate.Date;
    with Parameters.CreateParameter('enddate', ftDateTime,  ptInputOutput) do
      AsDate := DTP_EndDate.Date;
    // now perform the SQL on the database
    ExecSQL; // same as openning the query
  end;

Remember, that the SQL used in Delphi/ADO is NOT exactly that used in Access.
Check out the LOCALSQL help file (should be in C:\Program Files\Common Files\Borland Shared\BDE) or

http://sqlcourse2.com/

or

http://msdn2.microsoft.com/en-us/library/aa299742(SQL.80).aspx

(Micro$ofts Transact SQL reference)

Hope thats of help.

Cheers,
Chris

 

[topcat01]

Thanks Chris, I will give your suggestion a try.