• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1832
  • Last Modified:

select * from openquery(SERVER,'select * from table where date_time = ''4/23/2005'' ')

Hi,

I have got a linked server...and need to query data from a table by passing a date parameter....the following is the statement which i am using,

select * from openquery(SERVER,'select * from table where date_time = 4/23/2005')

and am getting the following error...

OLE DB provider 'MSDASQL' reported an error.  
[OLE/DB provider returned message: [SimbaLNA][Simba][SimbaEngine ODBC Driver]Error in predicate: date_time = 4 / 23 / 2005]
[OLE/DB provider returned message: [SimbaLNA][Simba][SimbaEngine ODBC Driver]Incompatible types in predicate.]


The table has got more than 100000 rows of data...

can anybody help me with this...its urgent...

Thanks in advance...
0
dvsameerkumar
Asked:
dvsameerkumar
  • 3
  • 3
  • 2
1 Solution
 
HilaireCommented:
please try

select * from openquery(SERVER,'select * from table where date_time = ''20050423''')
0
 
dvsameerkumarAuthor Commented:
Its not working

its throwing an error


An error occurred while preparing a query for execution against OLE DB provider 'MSDASQL'.
[OLE/DB provider returned message: [SimbaLNA][Simba][SimbaEngine ODBC Driver]Error in expression: 20050423]
[OLE/DB provider returned message: [SimbaLNA][Simba][SimbaEngine ODBC Driver]Invalid column name: '20050423'.]
0
 
dvsameerkumarAuthor Commented:
I think its got something to do with the way the date is passed...in double quotes or quotes is not working

must be something
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
MannaraprayilCommented:
its not double quotes...it should be two single quotes.....side by side
0
 
MannaraprayilCommented:
hope ur  date_time field is of type DATETIME.....

you can even try CONVERT function....
select * from openquery(SERVER,'select * from table where
CONVERT(VARCHAR(10),date_time,101) = ''04/23/2005'' ')

remember its not double quotes before and after 04/23/2005...its two single quotes put together.....
0
 
HilaireCommented:
the SQL Statements you send via openquery are executed remotely.
If the target server is NOT a MS SQL server , you'll have to submit keywords/functions that make sense on the remote DB.

Simba seems connected with Pervasive SQL dbs, but unfortunately i dont know the syntaxes used by this DBMS (it sounds like it has its own flavour of SQL)

Maybe you'll have more luck in the databases topic area ??
0
 
dvsameerkumarAuthor Commented:
this one worked

select * from openquery(SERVER,'select * from table where date_time = ''2005-04-23 00:00:00'' ')


Thank u guys
0
 
MannaraprayilCommented:

As a suggestion

It will be good to convert the date to a specific format before comparing them. Other wise you always need to compare it with long terms like ''2005-04-23 00:00:00''
It may fail if the date is having the time part which is different from 00:00:00

Functions like CONVERT is there for some reason.....
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now