Databases - Dates and Times

Most database applications use dates and times - this just covers the basics of how to manipulate them. Also see Fields and Calendars (how to pick a date from a calendar).

Delphi

Delphi 5.0

Delphi supports

Variable types: TDate, TTime, TDateTime, TTimeStamp
Field Types: TDateField, TTimeField, TDateTimeField

The first 3 types are Doubles, the third is a record (structure).

Dates before 12/30/1899 are negative (the OLE 2.0 standard).
In TTimeStamp, Time is stored as milliseconds after midnight.
In TDateTime, Time is stored as the fraction of a 24 hour day that has elapsed.
Some functions fail with dates before 1/1/100 AD.

Basic functions

dt := Date ; // today's date
dt := Time ; // Current time
dt := Now  ; // Current date and time

if IsLeapYear(1996)
day:= DayOfWeek(DateTime); // returns 1 thru 7

DecodeDate(Now, Year, Month, Day);
DecodeTime(Now, Hour, Min, Sec, MSec);
dt := EncodeDate(2003, 5, 20)  ; // Year, Month, Day
dt := EncodeTime(4, 15, 3, 500); // Hour, Min, Sec, MSec

These functions convert a DateTime to/from a string.

tempStr := DateToStr(DateTime)     ; // uses ShortDateFormat global variable
tempStr := TimeToStr(DateTime)     ; // uses LongTimeFormat  global variable
tempStr := DateTimeToStr(DateTime) ; // uses ShortDateFormat and LongTimeFormat

dt := StrToDate(tempStr);
dt := StrToTime(tempStr);
dt := StrToDateTime(tempStr);

  // These allow you to specify the format
DateTimeToString(tempStr, 'mmddyy', MonthCalendar1.date+1);
tempStr := FormatDateTime(formatStr, StrToDateTime('2/15/95 10:30am')) ;

Ther following global variables affect how dates and times are formatted (see Currency and date/time formatting variables in the help and sysutils.pas).

DateSeparator  : Char;
ShortDateFormat: string;
LongDateFormat : string;
TimeSeparator  : Char;
TimeAMString   : string;
TimePMString   : string;
ShortTimeFormat: string;
LongTimeFormat : string;

ShortMonthNames: array[1..12] of string;
LongMonthNames : array[1..12] of string;
ShortDayNames  : array[1..7]  of string;
LongDayNames   : array[1..7]  of string;

SysLocale      : TSysLocale;
EraNames       : array[1..7] of string;
EraYearOffsets : array[1..7] of Integer;

TwoDigitYearCenturyWindow: Word = 50;

These useful global variables are used in the Date/Time help example ... but they are not recognized by the compiler and they were not found in the supplied source code (ie, you can not use them).

DateFullYear := True;
DateLeadZero := True;

To determine the number of days in a month, use the undocumented global array MonthDays (this is not in the help).

x := MonthDays[IsLeapYear(Y), M] ; // M must be a value from 1 to 12

Database Controls

When database controls are placed on a form, they have a generic Field property. To use this as a double, just reference it (DBEdit1.Field.Value returns a variant); to use date/time specific properties, you must cast it (tell the compiler to treat the field as a TDateTimeField).

TDateTimeField.GetText defaults to

Date - ShortDateFormat
Time - LongTimeFormat

when DisplayFormat is not set.

Both of these commands will set the format used to display the date in an edit field (ie, they are equilalent).

(DBEdit1.Field as TDateTimeField).DisplayFormat := 'mmm dd';
TDateTimeField(DBEdit1.Field).DisplayFormat := 'mmm dd';

However, when you click in the field, the display shows the "edit" format - ShortDateFormat and LongTimeFormat. Notice that when you type the "dot" before DisplayFormat, Code Insight does not automatically display - instead, you must press Ctrl-Space to see the available options. DisplayFormat is not availble (will not compile) without the cast.

Missing Controls

Delphi 5 should contain several additional database controls

TDBDateTime: This would allow the developer to set the DisplayFormat property without code.
TDBCalendar: This would associate the existing Calendar control with a field. (Several free controls are available.)

Author: Robert Clemenzi - clemenzi@cpcug.org