May 1998

Date and time entries

Making database date and time entries in one field is problematic. Databases may provide fields designed for only a date, only a time, or for both. Some offer only the date and time option. Generally, the combined date and time field is another implementation of a floating-point field. The integer value represents a date and the fractional part represents time.

Day zero is what day?

TDateTime d(0);//set date to zero
String fs("mm/dd/yyyy hh:nn:sssss");
ShowMessage(d.FormatString(fs));

Here's the TQuery's SQL property:

SELECT e.StartedOn,
e.StartedOn AS StartedTime 
FROM 'employee.db' e

Now you can set up the OnSetText events for each field. The pseudo code for both is as follows:

DateField_OnSetText
Enter a valid date.
Check the time field.
  If time field is null
    Get current system time.
  Else
    Get time from the time field.
If dataset is in edit or insert mode
  Add the new time to the date entry. 

TimeField_OnSetText
Enter a valid time
Check the date field
  If date field is null
    Get current system date
  Else
    Get date from the date field
If dataset is in edit or insert mode
  Add the new time to the date field

Listing A: OnSetText event handlers for date and cloned time fields

void TimeField_SetText(TField *Sender, 
                       const AnsiString Text)
{ String DateEntry,TimeEntry;
  if (qEmployeeSTARTEDON->IsNull) //Date field null
    DateEntry = Date().FormatString("mm/dd/yyyy"); 
    //use today as default
  else
    DateEntry = 
      qEmployeeSTARTEDON->AsDateTime.DateString(); 
      //Get date value

  if(Text.IsEmpty())
    TimeEntry += 
      Time().FormatString("hh:nn"); //Current Time
  else
    TimeEntry += Text; //Use the entry

  DateEntry += " " + TimeEntry; //combine date, time
  Sender->AsString = DateEntry; //update text 
                                //to Time field

  TDataSet * ds = Sender->DataSet; //get data set 
                                  //object from field
  //if in edit mode, set date field to include time
  if (ds && (ds->State == dsInsert || 
    ds->State == dsEdit))
    qEmployeeSTARTEDON->AsDateTime = 
      StrToDateTime(DateEntry); //add time to 
                                //date field
}
//---------------------------------------------------

void DateSetText(TField *Sender, 
                 const AnsiString Text)
{ TDateTime d;
  String DateEntry(Text);
  if (!MakeValidDate(DateEntry, d, 4)) //date entry, 
                    //return variable, digits in year
  { ShowMessage("Please Enter a Valid Date");
    Abort();
  }
  else
  { DateEntry += 
      qEmployeeSTARTEDON->AsDateTime.FormatString(
      " hh:nn");
    Sender->AsString = DateEntry;
  }
}

It turns out the OnGetText event is ideally suited for this type of operation. The following code is an example of the OnGetText handler for the Date field:

void OnGetText(TField *Sender,
  AnsiString &Text,bool DisplayText)
{ const String dFmt ("mm/dd/yyyy");
  if(Sender->IsNull)
    Text=Date().FormatString(dFmt);
  else
    Text=Sender->AsDateTime.
                FormatString(dFmt);
}

Actually there isn't such a property in the TDBGrid. However, you can easily achieve your objective with a single line of code attached to the BeforeInsert event of a TTable or TQuery object:

void BeforeInsert(TDataSet *DataSet)
{
  ShowMessage("Aborted");//optional
  Abort();
}

Listing B: Creating tab-delimited text in a rich text object based on the contents of a Tdataset

void MakeReport(TDataSet *ds, TRichEdit *rte)
{ if (ds) //make sure dataset exists
  { int i;
    String s;

    const char tab = 9; //tab character

    ds->Active = false; //seems to be necessary 
                        //with text tables
    ds->Active = true;

    s = ds->Name; //get name of table
    s += "  "; //add space between table name 
               //and date/time
    s += Now().FormatString("dd mmmm yyyy hh:nn:ss");

    rte->Lines->Clear(); //clear contents
    rte->Lines->Add(s); //add table name, date, 
                        //and time to first line
    if (!(ds->Eof) &&(ds->Bof))
    { ds->First(); //reposition dataset on 
                   //first record
      int FieldCount = ds->FieldCount-1;
      while (!ds->Eof)
      { s="";
        for(i=0;i<=FieldCount;i++)
        { s += ds->Fields[i]->AsString;
          if (i<FieldCount) //don't add tab on 
                            //last field
            s += tab; //create tab-delimited string
        }
        rte->Lines->Add(s); //add text to 
                            //rich text object
        ds->Next();
} } } } 

void btnView_fCustomerClick(TObject *Sender)
{ //call mini-report code with TTable and TRichText object
  MakeReport(dynamic_cast<TDataSet*>(tbl), rte);
}

void CopyToClipBoard(TRichEdit *rte)
{ Screen->Cursor = crHourGlass;
  rte->SelectAll(); //select all text
  rte->CopyToClipboard(); //do copy
  rte->SelStart = 1; //unselect text
  rte->SelLength = 0;
  ShowMessage("Contents Copied");
  Screen->Cursor = crDefault;
}