SQL Example DateTime Range
From NexusWiki
Delphi Example of Query with DateTime Range
{TnxQuery component named qCLoans in TDatamodule named dmRep1}
uses Rep1; {dmRep1}
procedure PrintRep;
var
SavedCursor: TCursor;
begin
dmRep1.qCLoans.SQL.Clear;
dmRep1.qCLoans.SQL.Add(
'Select * from Loans L');
dmRep1.qCLoans.SQL.Add(
'WHERE (L.DateT>=:MinDate) AND (L.DateT<=:MaxDate) ');
wMinDate:=LoanReportForm.MinDateEdit.Date;
wMaxDate:=Int(LoanReportForm.MaxDateEdit.Date)+0.999999; // Only Max Date Entered, So Add High Time Value
dmRep1.qCLoans.ParamByName('MinDate').AsDateTime:=wMinDate;
dmRep1.qCLoans.ParamByName('MaxDate').AsDateTime:=wMaxDate;
if not dmRep1.qCLoans.Prepared then dmRep1.qCLoans.Prepare;
{Show SQL Hour Glass Cursor While Doing Query}
SavedCursor:=Screen.Cursor;
try
Screen.Cursor:=crSQLWait; {Or crHourGlass}
// dmRep1.qCLoans.SQL.SaveToFile('\SQLDump.txt'); {Uncomment To Dump SQL Statement For Debugging Purposes}
dmRep1.qCLoans.Open;
finally
Screen.Cursor:=SavedCursor;
end;
ppRepLoans.Print; {Print Report Using ReportBuilder}
dmRep1.qCLoans.Close;
end;
--Bestware 20:38, 13 January 2009 (UTC)
Sample Project
For a complete project example that shows how to use Delphi to dynamically build SQL statements based on dates, check out this project from Bill Mullen:
Sample Query to select by date
The nicely commented code for the main unit is shown below.
unit Unit1;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, nxsrSqlEngineBase, nxsqlEngine, nxsdServerEngine, nxsrServerEngine,
nxseAutoComponent, DB, nxdb, nxllComponent, Grids, DBGrids, StdCtrls, Buttons,
StrUtils;
// I am using an embedded server in this sample application so that you won't
// have to make any changes to this code or set anything up in your server
// installation - like creating an alias, etc.
const
// DDL (Data Definition Language) code to create the table "SampleTable"
CreateDatabaseSQL =
'CREATE TABLE "SampleTable" (' +
' "IDKey" AUTOINC NOT NULL,' +
' "AccountNumber" VARCHAR(10),' +
' "LastName" VARCHAR(25),' +
' "Date" DATE,' +
' "Status" INTEGER,' +
' CONSTRAINT "PK_Table1" PRIMARY KEY ("IDKey")' +
');';
// Code that will be parsed and corrected to generate the SQL code needed to
// populate "SampleTable" with sample data.
//
// #date1 will be replaced with a date exactly 6 months old (e.g. incMonth(date, -6))
// #date2 will be replaced with a date 1 day shy of six months old (e.g. incMonth(date+1, -6))
// #date3 will be replaced with a date 2 days shy of six months old (e.g. incMonth(date+2, -6))
// #date4 through #dateA will be replaced with a random date within the past four months
//
// Status is as follows:
// 1 = Available Accounts
// 2 = Accounts In Use
// 3 = Previous Accounts
PopulateDatabaseSQL =
'insert into sampletable (accountnumber, lastname,"date", status) values' +
'(''1072234'', ''lofton'', #date6, 3),' +
'(''1079826'', ''deshay'', #date7, 2),' +
'(''1085732'', ''williamson'', #date8, 2),' +
'(''1096183'', ''morrell'', #date9, 2),' +
'(''1102268'', ''habiger'', #date4, 3),' +
'(''1103945'', ''larsen'', #dateA, 1),' +
'(''1115821'', ''williams'', #date4, 2),' +
'(''1123315'', ''habiger'', #date6, 1),' +
'(''1124119'', ''christian'', #date5, 2),' +
'(''1130482'', ''williamson'', #date6, 1),' +
'(''1146341'', ''cordova'', #date7, 1),' +
'(''1147148'', ''bergmann'', #date8, 1),' +
'(''1171580'', ''hutton'', #date9, 2),' +
'(''1192706'', ''turner'', #dateA, 2),' +
'(''1210752'', ''cooper'', #date4, 3),' +
'(''1210984'', ''crowder'', #date5, 3),' +
'(''1219302'', ''ferrell'', #date6, 1),' +
'(''1223418'', ''ferrell'', #date7, 1),' +
'(''1223750'', ''turner'', #date8, 2),' +
'(''1256694'', ''hughes'', #date9, 2),' +
'(''1259437'', ''yeager'', #dateA, 3),' +
'(''1294087'', ''flusche'', #date1, 1),' +
'(''1326099'', ''barber'', #date3, 2),' +
'(''1330394'', ''glincher'', #date3, 3),' +
'(''1349762'', ''sheafer'', #date3, 1),' +
'(''1404953'', ''larsen'', #date3, 1),' +
'(''1575157'', ''busa'', #date3, 2),' +
'(''1604037'', ''bradford'', #date3, 3),' +
'(''1642581'', ''lindsey'', #date3, 3),' +
'(''1678471'', ''black'', #date8, 2),' +
'(''1710062'', ''miller'', #date3, 1),' +
'(''1777003'', ''van dijk'', #date3, 1),' +
'(''1897514'', ''lee'', #date2, 1),' +
'(''1989641'', ''pendergrass'', #date3, 2),' +
'(''2007243'', ''kim'', #date3, 2),' +
'(''2009808'', ''garibay'', #date3, 2),' +
'(''2083345'', ''gummer'', #date2, 1),' +
'(''2471471'', ''cannon'', #date1, 3),' +
'(''609308'', ''mikkel'', #date1, 1),' +
'(''610993'', ''copeland'', #date1, 2),' +
'(''628346'', ''higuet'', #date1, 2),' +
'(''641749'', ''christensen'', #date1, 2),' +
'(''649953'', ''mikkel'', #date1, 1),' +
'(''683012'', ''morris'', #date1, 2),' +
'(''707992'', ''morris'', #date1, 2),' +
'(''727734'', ''farris'', #date2, 2),' +
'(''759154'', ''hurley'', #date2, 2),' +
'(''776603'', ''hunter'', #date2, 1),' +
'(''776889'', ''hurley'', #date2, 2),' +
'(''822064'', ''bequeaith'', #date2, 1),' +
'(''825408'', ''wilson'', #date1, 1),' +
'(''831798'', ''bequeaith'', #date2, 1),' +
'(''833171'', ''lourance'', #date2, 1),' +
'(''842972'', ''talbot'', #date2, 2),' +
'(''888740'', ''lourance'', #date2, 1),' +
'(''932298'', ''salazar'', #date1, 2),' +
'(''953915'', ''salazar'', #date1, 2),' +
'(''968575'', ''sukenick'', #date3, 3),' +
'(''982276'', ''larsen'', #date4, 3),' +
'(''990236'', ''latimer'', #date5, 1);';
type
TAccountType = (atAll, atAvailable, atInUse, atPrevious);
TForm1 = class(TForm)
DBGrid1: TDBGrid;
DataSource1: TDataSource;
nxSession1: TnxSession;
nxDatabase1: TnxDatabase;
nxQuery1: TnxQuery;
nxseAllEngines1: TnxseAllEngines;
nxServerEngine1: TnxServerEngine;
nxSqlEngine1: TnxSqlEngine;
ComboBox1: TComboBox;
Label1: TLabel;
Memo1: TMemo;
SpeedButton1: TSpeedButton;
CheckBox1: TCheckBox;
SpeedButton2: TSpeedButton;
nxTable1: TnxTable;
procedure FormCreate(Sender: TObject);
procedure ComboBox1Change(Sender: TObject);
procedure SpeedButton1Click(Sender: TObject);
procedure SpeedButton2Click(Sender: TObject);
private
{ Private declarations }
procedure CreateDatabase;
procedure PopulateDatabase;
procedure BuildAndExecSQL;
public
{ Public declarations }
end;
var
Form1: TForm1;
implementation
{$R *.dfm}
function QuotedSQLDateStr(const ADate: TDate): string;
// returns a quoted SQL 2003 compliant date string
begin
try
DateTimeToString(Result, 'yyyy-mm-dd', ADate);
result := QuotedStr(result);
except
result := '';
end;
end;
procedure TForm1.FormCreate(Sender: TObject);
begin
// Set the alias path to the folder where this executable was run from
nxDatabase1.AliasPath := ExtractFilePath(ParamStr(0));
try
// Check to see if the "SampleTable.nx1" file exist. If not, create it.
// This ensures that the database and table are available and populated for
// this sample application.
if not FileExists(IncludeTrailingPathDelimiter(nxDatabase1.AliasPath) + 'sampletable.nx1') then
CreateDatabase;
// Make sure to select "All Accounts" in the combo box
ComboBox1.ItemIndex := ord(atAll);
// Create the SQL statement that will select the appropriate rows of data
// to display
BuildAndExecSQL;
except
// Something is screwed up so shut down the application
SendMessage(Application.Handle, wm_Close, 0, 0);
end;
end;
procedure TForm1.ComboBox1Change(Sender: TObject);
begin
// Only allow the Aged Accounts button to be enabled when the user has
// selected "Accounts In Use"
SpeedButton1.Enabled := TAccountType(ComboBox1.ItemIndex) = atInUse;
// Turn off sorting ("order by") simply because it is not needed when
// "In Use" accounts are selected
CheckBox1.Enabled := not SpeedButton1.Enabled;
// Make sure the Aged Accounts button is not in the down position if the
// type of accounts selected is not "In Use"
if TAccountType(ComboBox1.ItemIndex) <> atInUse then
SpeedButton1.Down := false;
// Create the SQL command needed to display the rows of data that match
// the users selections
BuildAndExecSQL;
end;
procedure TForm1.SpeedButton1Click(Sender: TObject);
begin
// Create the SQL command needed to display the rows of data that match
// the users selections
BuildAndExecSQL;
end;
procedure TForm1.SpeedButton2Click(Sender: TObject);
begin
// In your video, you had to change the date of your computer so that the
// appropriate rows of data would be displayed. Since this is just an example
// application and the data is really meaningless, this event empties the
// SampleTable table and populates it with fresh data so that there is data
// to match the Age Accounts criteria
try
nxTable1.Open;
nxTable1.EmptyTable;
nxTable1.SetAutoIncValue(0);
finally
nxTable1.Close;
end;
// Re-populate the database with meaningful data based on the current date
PopulateDatabase;
// Rebuild the SQL command and display the new data
BuildAndExecSQL;
end;
procedure TForm1.BuildAndExecSQL;
// Creates the SQL statement that will be used to select data from the sample
// table.
begin
try
// Temporarily stop updating the grid - speeds things up a bit
nxQuery1.DisableControls;
// Close the query so that we can build the new SQL command
nxQuery1.Close;
// Start by selecting every column and every row from the table
nxQuery1.SQL.Text := 'select * from sampletable';
// Add a "WHERE" clause if we need to limit the rows of data to return based
// on the Account Type ("Status" column in the table).
case TAccountType(ComboBox1.ItemIndex) of
atAvailable: nxQuery1.SQL.Text := nxQuery1.SQL.Text + ' where status = 1';
atInUse: nxQuery1.SQL.Text := nxQuery1.SQL.Text + ' where status = 2';
atPrevious: nxQuery1.SQL.Text := nxQuery1.SQL.Text + ' where status = 3';
end;
// Include code that will limit the data to 6 month of data (Aged Accounts)
// Note: SpeedButton1 can only be in the down position when the selected
// Account Type is "In Use" type accounts (Status = 2)
if SpeedButton1.Down then
nxQuery1.SQL.Text := nxQuery1.SQL.Text + ' and "Date" = Date ' +
QuotedSQLDateStr(incMonth(Date, -6));
// Add an ORDER BY clause if the Sort by Date checkbox is checked and enabled
// Note: The checkbox is disabled when the Aged Accounts button is in the
// down position
if (CheckBox1.Enabled) and (CheckBox1.Checked) then
nxQuery1.SQL.Text := nxQuery1.SQL.Text + ' order by "Date"';
// Show the SQL command that will be executed in the memo - for training
// purposes
memo1.lines.text := nxQuery1.SQL.Text;
// Open the Query component
nxQuery1.Open;
finally
// Turn on Grid updating so that the results of the SQL command can be seen
nxQuery1.EnableControls;
end;
end;
procedure TForm1.CreateDatabase;
begin
// Create the database/table
try
nxQuery1.SQL.Text := CreateDatabaseSQL;
// ExecSql is used to execute a SQL command when there are no rows of data
// returned. Examples of this is when you INSERT, UPDATE, or DELETE data
// from the database.
nxQuery1.ExecSql;
// Populate the database with meaningful data based on the current date
PopulateDatabase;
except
MessageDlg ('Unable to create database', mtError, [mbOK], 0);
Raise;
end;
end;
procedure TForm1.PopulateDatabase;
var
AString : string;
begin
Randomize;
try
// Before we can execute the SQL command to populate the data, we must
// replace the #date style macros with proper SQL code.
Astring := AnsiReplaceStr(PopulateDatabaseSQL, '#date1', format('date %s',[QuotedSQLDateStr(incMonth(Date, -6))]));
Astring := AnsiReplaceStr(Astring, '#date2', format('date %s',[QuotedSQLDateStr(incMonth(Date+1, -6))]));
Astring := AnsiReplaceStr(Astring, '#date3', format('date %s',[QuotedSQLDateStr(incMonth(Date+2, -6))]));
Astring := AnsiReplaceStr(Astring, '#date4', format('date %s',[QuotedSQLDateStr(Date - Random(120))]));
Astring := AnsiReplaceStr(Astring, '#date5', format('date %s',[QuotedSQLDateStr(Date - Random(120))]));
Astring := AnsiReplaceStr(Astring, '#date6', format('date %s',[QuotedSQLDateStr(Date - Random(120))]));
Astring := AnsiReplaceStr(Astring, '#date7', format('date %s',[QuotedSQLDateStr(Date - Random(120))]));
Astring := AnsiReplaceStr(Astring, '#date8', format('date %s',[QuotedSQLDateStr(Date - Random(120))]));
Astring := AnsiReplaceStr(Astring, '#date9', format('date %s',[QuotedSQLDateStr(Date - Random(120))]));
Astring := AnsiReplaceStr(Astring, '#dateA', format('date %s',[QuotedSQLDateStr(Date - Random(120))]));
// Now that we have a valid SQL command, execute it thereby populating the database;
nxQuery1.SQL.Text := Astring;
nxQuery1.ExecSql;
except
MessageDlg ('Unable to create database', mtError, [mbOK], 0);
Raise;
end;
end;
end.
