Using Records to avoid problems with database date/time interval

A record (analogous to a structure in some languages) represents a heterogeneous set of elements. Each element is called a field; the declaration of a record type specifies a name and type for each field.
The record type became more powerful in Delphi 2006 by adding support for:

• Constructors
• Non-virtual methods
• Static methods and properties
• and more.

Though records can now share much of the functionality of classes, there are some important differences between classes and records.

• Records do not support inheritance.
• Records can contain variant parts; classes cannot.
• Records are value types, so they are copied on assignment, passed by value, and allocated on the stack unless they are declared globally or explicitly allocated using the New and Dispose function. Classes are reference types, so they are not copied on assignment, they are passed by reference, and they are allocated on the heap.
• For more, check your RAD Studio documentation.

Record can help us in many different situations, for example in case you need to represent a database primary key, we can use Record for that helping to easy understand and maintain the code. It’s true you don’t keep changing your primary keys very frequently, but some day if it became necessary, it will facilitate the necessary changes. Another situation is to use Record to represent data interval and I will talk about that in this post.

Imagine the situation where you need to run a sales report filtered by period, and you have the follow method for that.

    procedure GenerateReport( InitialDate, FinalDate : TDateTime );

During my trips visiting customers, many of them report a common problem when we talk about date interval, specially when is related with reports. The reason for this problem is because the DATE value on the DATE field includes TIME and the applications just set the Date for the interval.

For many years the databases saved date and time on the same field, later they start to provide Data, Time and TimeStamp date types. Because the amount of data, systems and integration we can’t make update our database to represent this new data types, beside that those changes will impact many application. Considering we can’t make changes in our database, many of the Delphi Developers develop application to support more than one database, our application need to provide a mechanism to deal easily with this situation. We just need to make sure the date interval values will have ’00:00:00′ for the initial date and ’23:59:59′ final date, each database handle format for date/time differently.

Record is a excellent solution for this problem, instead to use two parameters for the method GenerateReport we will represent both as one Record and it will take care of the date/time interval values, making easy to read, understand and maintain the code.

The new method signature will change for::

    procedure GenerateReport( Interval : TDateInterval );

The record structure will include the properties StartDate and FinalDate, the Set methods will adjust the time value for both dates.

Below the record declaration.

unit Interval;

interface

uses SysUtils, DateUtils;

type

  TDateInterval = Record
  private
    FFinalDate: TDateTime;
    FStartDate: TDateTime;
    procedure SetFinalDate(const Value: TDateTime);
    procedure SetStartDate(const Value: TDateTime);

  public
    property StartDate: TDateTime read FStartDate write SetStartDate;
    property FinalDate: TDateTime read FFinalDate write SetFinalDate;

    Constructor Create(Id, Fd: TDateTime);

    procedure SetAnnualInterval( Iy, Fy : Integer );
  end;

implementation

{ TDateInterval }

constructor TDateInterval.Create(SYear, FYear: TDateTime);
begin
  StartDate := SYear;
  FinalDate := FYear;
end;

procedure TDateInterval.SetFinalDate(const Value: TDateTime);
begin
  FFinalDate := EncodeDateTime(Yearof(Value), MonthOf(Value), Dayof(Value), 23, 59, 59, 999);
end;

procedure TDateInterval.SetStartDate(const Value: TDateTime);
begin
  FStartDate := EncodeDateTime(Yearof(Value), MonthOf(Value), Dayof(Value), 0, 0, 0, 0);
end;

procedure TDateInterval.SetAnnualInterval(SYear, FYear: Integer);
begin
  StartDate := EncodeDate(SYear, 1, 1);
  FinalDate := EncodeDate(FYear, 12, 31);
end;

end.

• Any changes on StartDate and FinalDate properties will be adjusted to represent the correct time values
• There is a additional method named SetAnnualInterval, where you pass the period of years you would like for the reports, instead of the complete dates.

The sample below shows how you can use this Record to present the interval;

var
  interval: TDateInterval;
begin
  interval.StartDate := EncodeDate( 2009, 1, 1);
  interval.FinalDate := Now;

  GenerateReport(interval);

Another way is to pass the interval using the Record constructor.

var
  interval: TDateInterval;
begin
  interval.Create(EncodeDate( 2009, 1, 1), Now);
  GenerateReport(interval);

Also you can use the method SetAnnualInterval, where you specify a annual interval.

var
  interval : TDateInterval;
begin

  interval.SetAnnualInterval(2008, 2009);

  GenerateReport(interval);

In all cases the time was adjusted by the Set Methods,

We can add another features in this record, why note ask for the record to create the SQL where clause? Just two additional methods will provide that for us.

DBDateFormat formats the date and time values to add on the where clause. If the database uses different format, which I’m not representing the date/time format for every database on the following code, but after you read this post, you can download the source code and make the changes necessary to represent the database format. An additional parameter could be enough for this method, you can pass the SQL Connection for example and look at the driver property to identify how to format the date/time values. In this sample I don’t use parameter, but you can update the code later.

The method GenerateSQL has the Field parameter, which represents the table field.

function TDateInterval.DBDateFormat(const Value: TDateTime): String;
begin
  Result := FormatDateTime('mm/dd/yyyy hh:mm:ss', Value);
end;

function TDateInterval.GenerateSQL(Field: String): String;
Const
  sql: String = '%s between ''%s'' and ''%s'' ';
begin
  Result := Format(sql, [Field, DataDBFormat(StartDate), DataDBFormat(FinalDate)]);
end;

You use both method like this:

var
  period : TDateInterval;
begin

  period.SetAnnualInterval(2008, 2009);

  ShowMessage( period.GenerateSQL('DATE_FIELD'));

The ShowMessage will show DATE_FIELD between ’01/01/2008 00:00:00′ and ’12/31/2009 23:59:59′

I hope this visualizes another way to use Record, and that you find the date interval example useful. It can of course be implemented in many different ways, this is just one which I use to help in my explanation about Records.

You can download the source code here

TDateInterval

Related posts:

Android apps connected to Delphi DataSnap Server Sharing DB connection between multiples DataSnap Server Modules Parameterized queries with dbExpress (DBX) Framework Using Regular Expression on C++Builder FireMonkey Application