tags:

views:

1318

answers:

2

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
+1  A: 
 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.

eed3si9n
I getting a "missing operator" error when I run "..., cast(Date as varchar(20)) ..."
Austin Salonen
Date is a reserved word, so you might wanna double quote it.
eed3si9n
+1  A: 

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
I get a syntax error when using the SQL query with the MS ODBC driver. I've updated my question.
Austin Salonen
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