ODBC Date in OpenQuery

Question

Post reply

ODBC Date in OpenQuery

allen-623417

Mr or Mrs. 500

Points: 562
More actions
March 20, 2012 at 7:11 am

#391825

I'm trying to pull data from a Caché database using the InterSystems Caché ODBC driver but can't get a conditional date to work.
My openquery runs fine but I want to limit data to just the last two days in a DATE field.
This runs fine:
SELECT Name, Number, ODBCDate
FROM OPENQUERY(CACHEODBC,
'SELECT DISTINCT "TABLE"."Name", "TABLE"."Number", "TABLE"."ODBCDate"
FROM "SCHEMA"."TABLE" WHERE "TABLE"."ODBCDate"={d ''2012-01-19''}
')
But this returns an error:
SELECT Name, Number, ODBCDate
FROM OPENQUERY(CACHEODBC,
'SELECT DISTINCT "TABLE"."Name", "TABLE"."Number", "TABLE"."ODBCDate"
FROM "SCHEMA"."TABLE" WHERE "TABLE"."ODBCDate" >= convert(date,getdate()-2)
')
The error I get is:
OLE DB provider "MSDASQL" for linked server "CACHEODBC" returned message "[Cache ODBC][State : S1000][Native Code 400]
[E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\sqlservr.e]
[SQLCODE: <-400>:<Fatal error occurred>]
[Cache Error: <<ZCONV>msconvert+152^%qarfunc>]
[Details: <ServerLoop>]
[%msg: <Illegal DATE value (2010) passed to SQL CONVERT() function>]".
Msg 7320, Level 16, State 2, Line 1
Cannot execute the query "SELECT TOP 100 "TABLE"."Name", "TABLE"."Number", "TABLE"."ODBCDate"
FROM "SCHEMA"."TABLE"
WHERE "TABLE"."ODBCDate">CONVERT(date,getdate()-2)
" against OLE DB provider "MSDASQL" for linked server "CACHEODBC".
I've tried several variations of this, even tried to build a @query variable and dynamically pass it to the openquery with no success. :crying:
Anyone have suggestions how to get around this problem?
Thanks,
-Allen

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Lynn Pettis

SSC Guru

Points: 442467

March 20, 2012 at 8:42 am

#1461669

Try this ODBC date function: {fn CURRENT_DATE( )}

drew.allen SSC Guru Points: 76944 More actions · Answer 2

The error is coming from your Caché database, so you'll probably get a better answer from a Caché forum, but it looks like Caché doesn't support the DATE datatype.

Another possibility is that GETDATE() - 2 is a hack that happens to work in T-SQL, but is not guaranteed to work elsewhere. T-SQL has functions specifically for date arithmetic (DATEADD() and DATEDIFF()) and I assume that Caché has something similar. Try using that instead.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Here is a link to check out other options:

http://www.vieka.com/esqldoc/esqlref/htm/odbctime__date__and_interval_functions.htm

allen-623417 Mr or Mrs. 500 Points: 562 More actions · Answer 4

I found my answer..

If I use the ODBC function TIMESTAMPADD() I can get what I need.

I've added the following and get exactly what I was looking for:

>= {fn TIMESTAMPADD(SQL_TSI_DAY,-2,getdate())}

I got some pointers from this resource:

http://documentation.basis.com/BASISHelp/WebHelp/b3odbc/obdcdriv_time_and_date_functions.htm

Thanks for the help.

-Allen