1

First off, I'm a Paradox newbie.
Secondly, I'm querying a database of a third-party software package and cannot change the schema.

I have two fields simply named "Date" and "Time" that I'd like to query as a DateTime (from my MS SQL experience).

Is this possible?

I've tried several queries and, when the command is valid, I get "Data type mismatch in criteria expression."

Also, this would be from a Paradox database from about 1999-2000 if that makes any difference.

EDIT: Even a simple string concatenation of the fields would a great help because I could handle that in code.

EDIT: In response to a.i.breveleri's answer. I get this message:

ERROR [42000] [Microsoft][ODBC Paradox Driver] Syntax error (missing operator) in query expression 'CAST(m.DateComplete AS TIMESTAMP) - CAST([1/1/3000] AS TIMESTAMP) + CAST(m.TimeComplete AS TIMESTAMP)'.

When I run this query:

select distinct 
  CAST(m.DateComplete AS TIMESTAMP) - 
  CAST("1/1/3000" AS TIMESTAMP) + 
  CAST(m.TimeComplete AS TIMESTAMP)
from Mean m
Austin Salonen
  • 49,173
  • 15
  • 109
  • 139
  • Are you writing a QBE query or a SQL query? What are the Paradox data types of the "Date" and "Time" columns? -Al. – A. I. Breveleri Jun 25 '09 at 20:34
  • SQL query; my best guess on the data types are "Date" is a date column and "Time" is a string. – Austin Salonen Jun 25 '09 at 20:49
  • 1
    Sorry to be so dense, but are you using Paradox for Windows to access the data? If not, how are you connecting to the Paradox database? Other SO topics have indicated that a query like this can be sensitive to the choice of ODBC driver. -Al. – A. I. Breveleri Jun 25 '09 at 20:59

2 Answers2

1
 SELECT CAST(f.DateColumn AS VARCHAR(20)) + ' ' + CAST(f.TimeColumn AS VARCHAR(20)) 
 FROM Foo f

That gives you the concatenated string.

 SELECT CAST(CAST(f.DateColumn AS VARCHAR(20)) + ' ' + CAST(f.TimeColumn AS VARCHAR(20)) AS TIMESTAMP) As FooTime
 FROM Foo f

gives you the combined time.

Eugene Yokota
  • 94,654
  • 45
  • 215
  • 319
1

QBE query:

Query
ANSWER: :PRIV:ANSWER.DB

test.db | Date | Time                         | 
        | _x   | _y, calc _x+_y as "DateTime" | 

EndQuery

SQL query:

SELECT DISTINCT
    CAST(D0."Date" AS TIMESTAMP) -
    CAST("1/1/3000" AS TIMESTAMP) +
    CAST(D0."Time" AS TIMESTAMP)
FROM 
    "test.db" D0

-Al.

A. I. Breveleri
  • 325
  • 1
  • 3
  • I get a syntax error when using the SQL query with the MS ODBC driver. I've updated my question. – Austin Salonen Jun 26 '09 at 14:38
  • It looks like the Microsoft ODBC Desktop Database Driver (Probably ODBCJT32.DLL) is changing "1/1/3000" to [1/1/3000]. Try using single-quotes '1/1/3000'. Or you can check DataDirect Technologies (formerly Merant, maker of INTERSOLV ODBC drivers) for an alternate driver. -Al. – A. I. Breveleri Jun 26 '09 at 21:16