I have a Master/Detail Table pairing and I was planning to use a DateTime
stamp as a Key by which the associated Detail records could be found.

I have table joins like

tquery.SQL.add('SELECT * FROM DETAILTABLE, MASTERTABLE WHERE '+
                  DETAILTABLE.DateTimeKey = MASTERTABLE.DateTimeKey');

I've tried filling in a constant pasted directly from the Database
Desktop. I've tried to do the SQL in the Database Desktop.  In each
of these cases the result set has no records when it should contain 4.

The Delphi manuals are very sparse about SQL in general and about use
of DateTime fields as indexes in particular.

Has anyone done this successfully?

BTW. I've had not problems with fields of type integer, Float or String.

Douglas P. Webb                    | Life is a Game
CETAC Technologies Inc.            | The object of which is . . .
dw...@cetac.com    402-733-2829    | To find out the Object of the Game
Views expressed here are mine and do NOT reflect CETAC company policy.

<problem description snipped>

What I'm doing is acquiring data from an instrument, and when the last
of the 4 data points has been acquired I do something like this:

CalTime := Now;

MASTERTABLE.insert;
MASTERTABLE.FieldByName('DateTimeKey').AsDateTime := CalTime;
<put other data in the master record>
MASTERTABLE.post;

Then I store the details using a loop:

For i := 1 to MAXSAMPLE do
  begin
  DETAILTABLE.insert;
  DETAILTABLE.FieldByName('DateTimeKey').AsDateTime := CalTime;
  <store data in other fields>
  DETAILTABLE.post;
  end;

So the values are all from the same source.

I'm using a Paradox 5.0 format table.  The fields in question are of
type DateTime  (in the Database Desktop the field symbol is @).

Yesterday I confered with someone else and he's had the same problem -
he eventually gave up and built his own datestamp key type.

dw...@cetac.com (Douglas P. Webb) wrote:

That's pretty weird.  My only guess is that maybe Delphi (that is, the
BDE) is trying to interpret the SQL and maybe is mapping the
DateTimeKey fields to some internal DateTime type.  If the type in the
database is different than the type the BDE may be trying to use, then
any unusualness in the conversion of those values may be causing the
records not to match.

Wait a minute, are those true DateTimeStamp type fields?  Like in SQL
Server or something?  If so, the the DateTimeStamp's probably are
different, because they're guaranteed to be unique (since two records
are never updated at _exactly_ the same time). . .

We've just got SQL Server installed, so I'll try something like that
as a test once we get it configured.

What type of database are you using for this?

Best,
Chris Crabtree

"Talking about music is like dancing about architecture."
   --William S. Burroughs

Douglas P. Webb (dw...@cetac.com) wrote:
: I have a Master/Detail Table pairing and I was planning to use a DateTime
: stamp as a Key by which the associated Detail records could be found.

: I have table joins like

: tquery.SQL.add('SELECT * FROM DETAILTABLE, MASTERTABLE WHERE '+
:                   DETAILTABLE.DateTimeKey = MASTERTABLE.DateTimeKey');

I found the following local SQL statement to work fine in linking two
Paradox 5.0 tables via Timestamp fields:

  SELECT P."PStamp" , C."CStamp"
  FROM "PSTAMP.DB" P, "CSTAMP.DB" C
  WHERE ( P.PStamp = C.CStamp )

In this example, PSTAMP.DB is the master table, while CSTAMP.DB the
detail table. The data for the Timestamp fields was produced in a
manner the same as yours: storing the return value from the Now function
into a variable, adding a single record to the master table and multi-
ple records to the detail, the field value for the Timestamp fields
coming from the variable.

: I've tried filling in a constant pasted directly from the Database
: Desktop. I've tried to do the SQL in the Database Desktop.  In each
: of these cases the result set has no records when it should contain 4.

: The Delphi manuals are very sparse about SQL in general and about use
: of DateTime fields as indexes in particular.

: Has anyone done this successfully?

: BTW. I've had not problems with fields of type integer, Float or String.

: Douglas P. Webb                    | Life is a Game
: CETAC Technologies Inc.            | The object of which is . . .
: dw...@cetac.com    402-733-2829    | To find out the Object of the Game
: Views expressed here are mine and do NOT reflect CETAC company policy.

--
_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
_/ Steve Koterski               _/   The opinions expressed here are    _/
_/ koter...@borland.com         _/         exclusively my own           _/
_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/

Douglas P. Webb (dw...@cetac.com) wrote:
: I have a Master/Detail Table pairing and I was planning to use a DateTime
: stamp as a Key by which the associated Detail records could be found.

: I have table joins like

: tquery.SQL.add('SELECT * FROM DETAILTABLE, MASTERTABLE WHERE '+
:                   DETAILTABLE.DateTimeKey = MASTERTABLE.DateTimeKey');

: I've tried filling in a constant pasted directly from the Database
: Desktop. I've tried to do the SQL in the Database Desktop.  In each
: of these cases the result set has no records when it should contain 4.

I have had exactly the same problem when converting the datetime
(which is part of the primary key of another table) into TDateTime
and then using it in my SQL statement - the value is unmodified but
the SQL refuses to pick up the correct data. I assumed that due to
an inaccuracy in the TDateTime (which is a Double as far as I know,
and therefore likely to be inaccurate) then is a matching problem...
I just gave up with their date/time field and broke them into two...

Richard
--
"If we confine the concept of weeds to species adapted to human disturbance,
then humankind is by definition the first and primary weed under whose
influence all other weeds have evolved."
      Jack R. Halston, Crops and Man (1975)

Hi There,
        Not that I am any kind of expert but...what I would try is :-
                        MASTERTABLE.DateTimeKey.AsDateTime));

Many Reards.....

--
John M. Kelly

In article <3upcd9$...@westie.mid.net>, dw...@cetac.com says...
I don't know which database you are using, but with Sybase there is a
function called datediff which allows you to specify the difference you
will tollerate..  eg datediff(second, onedate, theotherdate) = 0.

I have used this to overcome the difference in milliseconds between the
way Sybase and Delphi see the ??same date/time.

Hope this helps....

Lesley