Board index » delphi » MS SQL Server 7 date/time fields

MS SQL Server 7 date/time fields

I am porting a Delphi 5 application that uses dBase IV data base tables
to work with MS SQL Server 7 via ODBC. SQL Server 7 does not seem to
have a pure date data type, only a date/time data type. I have two
problems:

1) My application, based in dBase IV, uses TDateField components to
reference all dates in the data base. SQL Server 7 via ODBC fills these
fields with supposed garbage. Do I have to convert these fields to
TDateTimeField components to work with SQL Server? I would like this
application to be portable between both local and server-based data
bases. Is there a way to make TDateTimeField components work with local
data bases such as dBase IV, or do I have to build two different
versions of my application, one for local data bases and one for
server-based data bases?

2) When I use an edit mask in a data-aware TDBEdit control that displays
only the date part (referencing a TDateTimeField component), SQL Server
refuses to update a record because I have an invalide date/time format.
How can I use SQL Server 7 date/time fields as dates only (no time
component)?

Any help in the above would be muchly appreciated.

Stuart Adamic
Essex Data Services Ltd.

 

Re:MS SQL Server 7 date/time fields


Hi,

I can help you on issue (1) 'cos I had the exact same thing with Oracle.
Now I've got one single app that runs against Paradox or Oracle, and
configures itself accordingly.  This is how you do it....

EITHER you ditch ALL your instantiated fields - if you go down this road you
heve to ditch all of them, not just the Date/DateTime ones, because the
Date/DateTime ones will then become invisible to the app if you instantiate
only a few of the fields.  Delphi instantiates them all dynamically when you
fire up your app, and it looks at the data and works it all out for you, but
you lose the benefits(?) of having your fields instantiated up front.

OR (this is what I did) you ditch only the instantiated fields that are
Dates and then instantiate them in code either as TDateTime or as TDate
fields (depending on the back end database) So you'll need a flag somewhere,
let's say its "adbs" (I made a Tdbs type to cope with this set of values)
and some constants which are either Paradox (dbsPDX) or ClientServer
(dbsORA, dbsMSSQL) - you need to set the value of the "adbs" flag by reading
its value from an INI file or something, to tell the system "Am I Paradox?"
or "Am I ClientServer?" when you initialise your DataModule (or form).  To
instantiate fields in code, you do this kind of thing:

   procedure CreateAndSetDateTime(adbs: Tdbs; var Field: TField;
                           sName: String; Owner: TObject; iIndex: Integer;
                           sLabel: String; iWidth: Integer;
                           sDataSet: String = '');
   const
      sERR = 'Create And Set Date Time Error'#13#10+
             'Attempt to create TField with nil owner';
   var
      theDataSet: string;
   begin
      if Owner = nil
      then raise EDefFldsError.create(sERR);
      //  Destroy the OLD Field first prevent possible Memory Leak
      if (TFieldType(Field) = ftDate) or
         (TFieldType(Field) = ftDateTime) then begin
         Field.free;{ will destroy if allocated etc}
      end;
      if adbs = dbsPDX then begin
         if Owner is TDataModule
         then Field := TDateField.Create(TDataModule(Owner))
         else Field := TDateField.Create(TForm(Owner));
      end else begin
         if Owner is TDataModule
         then Field := TDateTimeField.Create(TDataModule(Owner))
         else Field := TDateTimeField.Create(TForm(Owner));
         Field.OnGetText := DefFlds.DateGetText;
      end;
      if Owner is TDataModule then begin
         with Owner as TDataModule do begin
           Field.FieldName := sName;
           Field.DataSet := TDataset(FindComponent(theDataSet));
           Field.DisplayWidth := iWidth;
           Field.DisplayLabel := sLabel;
           Field.Index := iIndex;
        end;
      end else begin
         with Owner as TForm do begin
           Field.FieldName := sName;
           Field.DataSet := TDataset(FindComponent(theDataSet));
           Field.DisplayWidth := iWidth;
           Field.DisplayLabel := sLabel;
           Field.Index := iIndex;
        end;
      end;
   end;

Pretty ugly, I know, because you also have to decide if the data set is on a
TDataModule or on a TForm, but it works, and once you got it working you can
forget about it.

Call this routine like this:

in the form's public part, do this:

  public
   { Public declarations }
   //fields instantiated at runtime...
   TABLENAMEFIELDNAME: TField; // because the complier doesn't know if its
TDate or TDateTime yet

in the Form's OnCreate event handler do this:

myPurchaseForm.FormCreate(Sender: TObject);
begin
   CreateAndSetDateTime(adbs, TABLENAMEFIELDNAME, 'FIELDNAME',
myPurchaseForm, 1, 'PurchaseDate', 10);
end;

regarding issue (2) I had to ditch my data aware controls for dates,
replacing them with TEdit components and writing code to handle all the
input/output etc.  Not much fun. Who said upsizing was easy?

By the way, I had to do all this instantiating and so on for boolean fields
too, imagine the fun I had with data aware checkboxes not working, because
Oracle hasn't got a boolean data field, so I had to change all them to
TCheckboxes and write code to send a 'T' or 'F' to the server to denote True
or False.

hope this helps.

Paul Harding.

Quote
Stuart Adamic wrote in message <3880D8D5.5358E...@v-wave.com>...
>I am porting a Delphi 5 application that uses dBase IV data base tables
>to work with MS SQL Server 7 via ODBC. SQL Server 7 does not seem to
>have a pure date data type, only a date/time data type. I have two
>problems:

>1) My application, based in dBase IV, uses TDateField components to
>reference all dates in the data base. SQL Server 7 via ODBC fills these
>fields with supposed garbage. Do I have to convert these fields to
>TDateTimeField components to work with SQL Server? I would like this
>application to be portable between both local and server-based data
>bases. Is there a way to make TDateTimeField components work with local
>data bases such as dBase IV, or do I have to build two different
>versions of my application, one for local data bases and one for
>server-based data bases?

>2) When I use an edit mask in a data-aware TDBEdit control that displays
>only the date part (referencing a TDateTimeField component), SQL Server
>refuses to update a record because I have an invalide date/time format.
>How can I use SQL Server 7 date/time fields as dates only (no time
>component)?

>Any help in the above would be muchly appreciated.

>Stuart Adamic
>Essex Data Services Ltd.

Other Threads