Board index » delphi » Problem with datetime and Paradox Query

Problem with datetime and Paradox Query

I am running a query with this criteria
WHERE CallBackTime <= 10/12/1999 10:00:14 PM
but paradox does not see it as a datetime value.  I tried putting '' around
it and nothing, CAST did not work either.

Is there some character that I need to use (like the # in MSACCESS). ? the
localsql help file was not much help with this one.

TIA
Jorge Montas

 

Re:Problem with datetime and Paradox Query


use parameterised queries, for example

WHERE
  CallBackTime <= :DateTime

Then, you have to prepare the query yourself, otherwise the sql will
raise an error when you try to open the query.

query.prepare;

you then need to use parambyname to populate the DataTime parameter,
like:

query.parambyname('DateTime').asDateTime:=strtodate('01/05/99
10:00');

by default strtodate uses shortdate dateformat / time that you have
specified in control panel -> regional settings

now,you can open the query.  

Quote
Jorge Montas wrote:

> I am running a query with this criteria
> WHERE CallBackTime <= 10/12/1999 10:00:14 PM
> but paradox does not see it as a datetime value.  I tried putting '' around
> it and nothing, CAST did not work either.

> Is there some character that I need to use (like the # in MSACCESS). ? the
> localsql help file was not much help with this one.

> TIA
> Jorge Montas

--
Stewart
ICQ: 5308166
http://freespace.{*word*269}.net/s.hector/index.html

Re:Problem with datetime and Paradox Query


On Wed, 6 Oct 1999 12:05:25 -0800, "Jorge Montas" <jmon...@minicompsa.com>
wrote:

Quote
>I am running a query with this criteria
>WHERE CallBackTime <= 10/12/1999 10:00:14 PM
>but paradox does not see it as a datetime value.  I tried putting '' around
>it and nothing, CAST did not work either.

>Is there some character that I need to use (like the # in MSACCESS). ? the
>localsql help file was not much help with this one.

Stewart Hector points out one viable option, using a parameter to pass the
date value to the SQL statement. But it is an option, not a requirement.
Local SQL will accept DATE, TIME, and TIMESTAMP literals in statements --
if they are properly formatted.

Enclosing date literals in quotation marks (single or double) prevents
their being mistaken by the SQL engine for an arithmetic calculation. For
example, your date of 10/12/1999, without the quotation marks, could be
mistaken for 10 divided by 12 divided by 1,999. Not what you intended. So
the correct syntax would be:

  SELECT ...
  FROM ...
  WHERE CallBackTime = "10/12/1999 10:00:14 PM"

But then, in local table types, dates are stored as floating point number.
The inexactness of these type numbers and differing capabilities to handle
them on different computers can cause problems when comparing two such
numbers. One way around that is to use the SQL function CAST to force the
DATE or TIMESTAMP column to be treated as CHAR and make the comparison with
a CHAR value. For example:

  SELECT ...
  FROM ...
  WHERE CAST(CallBackTime AS CHAR(22)) = "10/12/1999 10:00:14 PM"

==========================================================================
Steve Koterski                  "Computers are useless. They can only give
Technical Publications          you answers."
Borland                                       -- Pablo Picasso (1881-1973)
http://www.borland.com/techpubs/delphi

Other Threads