Board index » delphi » 2 Local SQL Problems

2 Local SQL Problems

l...@mosoft.se (Lars G ?rne) wrote:

Quote
>Something for all you SQL gurus to chew on:
>1. Usage of Date/Time fields (Paradox Table)
>Working inside the Database Desktop:
>SELECT * From BokData
>This gives me a column with the date/time-field StartTid having some
>rows containing "08.30.00, 1996-02-05"
>but this
>SELECT * From Bokdata
>where Starttid="08.30.00, 1996-02-05"
>gives me the error message: "Expression in this field has the wrong
>type"
>I've tried turning this statement around in all possible ways, but it
>just doesn't work. What is wrong ??
>2. Same Paradox tables
>This works:-
>SELECT * From Bokdata,Bokning,Aktiv,Produkt,Kund
>Where Bokning.BokningNr=Bokdata.BokningNr
>And Bokning.Aktiv=Aktiv.AktNr
>And Bokning.Prod=Produkt.ProdNr
>And Bokning.Kund=Kund.KundNr
>but wher I add this line, the Query DLL comes crashing down on me
>And EXTRACT(YEAR FROM Bokdata.StartTid)=1996
>This also works fine
>SELECT * From Bokdata,Bokning
>Where Bokning.BokningNr=Bokdata.BokningNr
>And EXTRACT(YEAR FROM Bokdata.StartTid)=1996
>Have I come across some limit in Local SQL or BDE?
>/LG

Since I got no responses to these questions, I'll publish the
workaround solution I stumbled on by accident:

...
var
        DT1, DT2: TDateTime;

begin
  ...
        Query1.SQL.Add('And Bokdata.StartTid>='+Format('%g',[Dt1]));
        Query1.SQL.Add('And Bokdata.slutTid<'+Format('%g',[Dt2]));

  ...
end;
...

In other words - treat the DB DateTime value as a numeric (double)
value.

A few questions still remain -
1. Is this the accepted way to work with Date/Time in the BDE?
2. Is it transparant to the underlying DB?
3. What about ODBC - can I use this solution when/if my app is ported
to use ODBC data sources?

I can't find any information on this subject in the so-called manuals
or in the OLH.

Any feedback on this would be appreciated (Borland - where are you?)

/LG
____________________________________________________
Lars G. Oerne   (SM7BLJ)     MoSoft
P.O.Box 72                   S-340 36 MOHEDA Sweden
Cell Phone +46 0705441225    "The House of FRITSON"
email l...@mosoft.se
My views are the same as my employers'. They better be...
____________________________________________________

 

Re:2 Local SQL Problems


On Wed, 14 Feb 1996 07:41:28 GMT, l...@mosoft.se (Lars G ?rne) wrote:

Quote
>l...@mosoft.se (Lars G ?rne) wrote:

[...]

Quote
>...
>var
>    DT1, DT2: TDateTime;

>begin
>  ...
>    Query1.SQL.Add('And Bokdata.StartTid>='+Format('%g',[Dt1]));
>    Query1.SQL.Add('And Bokdata.slutTid<'+Format('%g',[Dt2]));

>  ...
>end;
>...

>In other words - treat the DB DateTime value as a numeric (double)
>value.

>A few questions still remain -
>1. Is this the accepted way to work with Date/Time in the BDE?
>2. Is it transparant to the underlying DB?
>3. What about ODBC - can I use this solution when/if my app is ported
>to use ODBC data sources?

>I can't find any information on this subject in the so-called manuals
>or in the OLH.

The DateTime field in a Paradox table (and the corresponding Delphi data
type) is a real. The numbers to the left of the decimal represent the
number of days since a baseline date (the exact date escapes me at the
moment), and the digits to the right the number of seconds since midnight
(for that day).

But Delphi automates the process of dealing with dates through the
TDateTime data type and such functions as DateTimeToStr, StrToDateTime, and
FormatDateTime.

If you are going to build your SQL statement manually, you can use the
FormatDateTime function to format a TDateTime value into the proper format
that can be used in an SQL query against a DateTime field. Make sure to
enclose the TDateTime value (converted to a string) in double quotes. For
example:

  Query1.SQL.Add('AND Bokdata.StartTid >= "' +
    FormatDateTime('mm/dd/yyyy hh:mm:ss', Dt1) + '"');

This would result in a line such as:

  AND Bokdate.StarTid >= "05/08/1995 00:01:00"

If, instead, you use a parameterized query, the parameter should be of type
DateTime and it would be populated by simply storing to it the
(unconverted) TDateTime variable's value:

  Query1.Params[0].AsDateTime := Dt1;

**************************************************************************
Steve Koterski                  "Results! Why, man, I have gotten a lot of
Product Group Manager           results. I know several thousand things
Delphi Technical Support        that won't work."
Borland International, Inc.                    -- Thomas Edison, 1847-1931

Other Threads