Passing Dates to SQL

TimCS.Co.Uk

SQL in Delphi is very limited, as discovered recently by myself, on a project that is in development. To get around this limitation, Delphi has a host of Date functions in a unit called dateutils. This needs to be added to the standard list of the units in the uses clause that is near to the begining of a Delphi application. E.g:

Code:

unit Unit1;

interface

uses Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms, Dialogs;

So on adding the dateutils to this section it should look something like this :

Code:

unit Unit1;

interface

uses Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms, Dialogs,dateutils;

This addition to the uses clause now allows Delphi to use date functions when programming. This is needed in order to pass dates to SQL.

So to do this using a string variable called Sqlcode , the following code shows how to pass a date to SQL :

Code:

SqlCode := ''";
Sqlcode := 'Select table1.* where table1.number = table2.number';
Sqlcode := sqlcode +' and table2.date >= '+quotedstr(formatdatetime('mm/dd/yyyy',firstday))+' and table2.date <= '+quotedStr(formatdatetime('mm/dd/yyyy',lastday));

The references firstday and lastday are date variables that represent the first and last day of a given month. The function quotedstr is used to put quotes around the date and the formatdatetime function, simply formats the given variable(s) (in this case firstday and lastday) to the format that is accepted by SQL.