Board index » delphi » SQL adding date and time fields

SQL adding date and time fields

I have a paradox table that I am trying to write an SQL query for.  I am
querying for records that fall between certain dates and times.  The date
and time are stored in separate fields.  The date being a date type and
time being a time type.  I want to add the date and time fields together
but I get the error message "Type mismatch in expression"  How can I add
the date and time fields for query purposes?  Thanks.

Chris

 

Re:SQL adding date and time fields


On 2 Aug 1999 15:11:22 GMT, "Christopher Voth" <chris.v...@solar.com>
wrote:

Quote
>I have a paradox table that I am trying to write an SQL query for.  I am
>querying for records that fall between certain dates and times.  The date
>and time are stored in separate fields.  The date being a date type and
>time being a time type.  I want to add the date and time fields together
>but I get the error message "Type mismatch in expression"  How can I add
>the date and time fields for query purposes?  Thanks.

What is the local SQL statement you were trying to use and that caused the
error? (Please post here in the newsgroup.)

One thing you might try is concatenating the two values. As only CHAR
values can be concatenated using the concatenation function, use the CAST
function to convert the DATE and TIME columns to CHAR. Then, use CAST again
to convert the concatenated value to type TIMESTAMP.

  SELECT *
  FROM YourTable
  WHERE CAST(CAST(YourDate AS CHAR(10)) || " " || CAST(YourTime AS
    CHAR(12)) AS TIMESTAMP) BETWEEN "01/01/1999 08:00:00AM" AND
    "06/30/1999 05:00:00PM"

Local SQL (what the BDE uses for dBASE, Paradox, and FoxPro tables) is
documented in the online help file LOCALSQL.HLP, found in the main BDE
directory. This help file is a language reference of the local SQL
implementation (subset) of SQL-92. The copy of this file that came with BDE
4.x (and earlier versions) was outdated and has since been rewritten.
Updated copies will have the topic "Unsupported language" in the index (and
lack of this topic indicates a pre-update copy). I can e-mail you a copy of
the updated file if you need and desire it.

_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
Steve Koterski         "Television is a medium because anything well done
Felton, CA             is rare."
                                                 -- Fred Allen (1894-1956)

Re:SQL adding date and time fields


Steve

I had been trying exactly what you suggested but I was never able to get
the syntax right.  I cut and pasted yours in and it worked.  I thank you
very much.  After I posted the message I was able to do the same thing with
a whole bunch of ands and ors and a ton of parenthesis.  This will be much
cleaner though.  Thank you again for your help.

chris

Other Threads