Board index » delphi » SQL Queries against a DateTime Field

SQL Queries against a DateTime Field

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.

 

Re:SQL Queries against a DateTime Field


<problem description snipped>

Quote
>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). . .

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.

Quote
>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?

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.

Re:SQL Queries against a DateTime Field


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

Quote
>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.

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

Re:SQL Queries against a DateTime Field


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           _/
_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/

Re:SQL Queries against a DateTime Field


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)

Re:SQL Queries against a DateTime Field


Hi There,
        Not that I am any kind of expert but...what I would try is :-
Quote

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

                        MASTERTABLE.DateTimeKey.AsDateTime));

Quote

Many Reards.....

--
John M. Kelly

Re:SQL Queries against a DateTime Field


In article <3upcd9$...@westie.mid.net>, dw...@cetac.com says...
Quote

>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

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

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

Other Threads