• Dates and Times - Functions that work with Dates and Times, includes field info.
• Calendars and Dates - How to pop-up a calendar so that you can select a date.

  '8/19/2004  3:19:43 PM' Fails
  '8/19/2004 15:19:43'     OK
  'now'                   current datetime

• In Delphi, the now function returns a TDateTime value

• In Firebird, the 'now' string can be used as a Date/Time literal in queries

  DateTime := DateTimeToStr(Now);

     //   CREATE TABLE "xyz_tbl" (
     //     "xyz_ID"    VARCHAR(5) NOT NULL /* "Experiment_ID" */ ,
     //     "Date_Time" TIMESTAMP           /* "Date_Time" */
     //   );

  s :=
     'INSERT into "xyz_tbl" ' +
     '("xyz_ID", "Date_Time")' +
     ' Values (''' +
          Experiment_ID + ''', ''' +
          DateTime      + ''''     +
     ' ) '
    ;
  ExecuteSQL(s);  // a routine I wrote

LongTimeFormat = 'h:mm:ss AMPM'

'1/30/2005 3:06:32 AM'

Notice that LongTimeFormat uses mm for both month and minutes, but that nn always produces minutes. From the Delphi help

mm  Displays the month as a number with a leading zero (01-12). 
    If the mm specifier immediately follows an h or hh specifier, 
    the minute rather than the month is displayed.

Here are some miscellaneous instructions used to format date/times

DT_Str := Date + ' ' + Time;
DT     := StrToDateTime(DT_Str);
DateTimeToString(DT_Str, 'mm/dd/yyyy hh:mm:ss am/pm', DT);// ok for display, not for Firebird
DateTimeToString(d, 'yyyymmdd', DT);
DateTimeToString(m, 'yyyymmdd_hhnnss', DT);

Are values stored as text values stored as 12 hour or 24 hour values?

// This routine accepts any valid date/time format
// and produces a date/time in 24 hour format
// without the am/pm specifiers
// Firebird queries fail if am or pm is present
function TBasicDataModule.DateTime_to_24hr(DateTime: string): string;
var
  dt_str : string;
  dt : TDateTime;
begin
  if CompareText(DateTime, 'now')=0 then begin  // case insensitive
    dt_str := FormatDateTime('mm/dd/yyyy hh:nn:ss', Now);
  end else begin
    dt     := StrToDateTime (DateTime);
    dt_str := FormatDateTime('mm/dd/yyyy hh:nn:ss', dt);
  end;

  result := dt_str;
end;

"What time should be stored in the database?"

• You are calculating elapsed time and the clocks automatically convert to (from) daylight savings time
• You are coordinating people located in different time zones (such as tracking objects orbiting the earth)

• Greenwich Mean Time (GMT)
• Coordinated Universal Time (UTC)
• Zulu

• GetSystemTime retrieves the current system date and time expressed in Coordinated Universal Time (UTC)
• SystemTimeToTzSpecificLocalTime converts a Coordinated Universal Time (UTC) to a specified time zone's corresponding local time

Local DateTime	Used with the user interface
UTC DateTime	Simplifies computations
UTC offset in effect at that location on that day

References

• World Time Scales
• US Law related to daylight savings time - Daylight savings time starts at 2:00 AM on the first Sunday of April and ends at 2:00 AM on the last Sunday of October for those states that allow it. (several don't) This law is currently being changed - expect lots of problems.

URL: http:// cpcug.org / user / clemenzi / technical / Databases / Delphi / Firebird / Dates.html